Update Other Table Based On A Table Value In Sql

I have two tables named Employee and Time. I want to assign OnTrack=y if all the TaskList of employee code 1E are y. I tried this and I am stuck UPDATE Employee SET OnTrack = 'y' F

Solution 1:

I've created some table variables with test data. How about this:

DECLARE@TimeTABLE (Code VARCHAR(2),Employeecode VARCHAR(2),Status CHAR(1), 
                     WorkingHours INT, TaskList CHAR(1))
INSERT@TimeVALUES  ('1A','1E','P',8,'Y'), 
        ('2A','1E','P',8,'Y'),  
        ('3A','1E','P',8,'N'), 
        ('4A','2E','T',8,'Y'),
        ('5A','2E','T',0,'Y'),
        ('6A','3E','P',8,'Y')
DECLARE@EmployeeTABLE (Code CHAR(2), Name VARCHAR(10), Hours INT, OnTrack CHAR(1))
INSERT@EmployeeVALUES  ('1E','SCOTT',32,NULL),
        ('2E','LISA',32,NULL),  
        ('3E','MARK',32,NULL)

UPDATE e
SET e.OnTrack = t.TaskList
FROM@Employee e
JOIN (SELECT Employeecode, MIN(TaskList) TaskList
      FROM@TimeGROUPBY Employeecode) t ON E.Code = T.Employeecode

SELECT*FROM@Employee

Results:

Code    Name    Hours   OnTrack
1E      SCOTT   32      N
2E      LISA    32      Y
3E      MARK32      Y

If you only want to update permanent employees, just add the Status to the subquery.

SELECT Employeecode, MIN(TaskList) TaskList
    FROM@TimeWHERE Status ='p'GROUPBY Employeecode

Solution 2:

(Assuming you are using SQL Server) Try this select stmt first and if like the results use the update stmt down below. Its hard to write code without seeing the tables but just going by your explanation this should put you in the right direction.

select*FROM Employee e 
INNERJOINTIME t1 ON t1.EmployeeCode  =   e.code 
INNERJOINTIME t2 ON t2.EmployeeCode  <>  e.code 
WHERE t1.status    ='P'AND   t2.tasklist <>'y'

Dont run this without trying the above select

UPDATE Employee
SET OnTrack ='y'FROM Employee e 
INNERJOINTIME t1 ON t1.EmployeeCode   = e.code 
INNERJOINTIME t2 ON t2.EmployeeCode  <> e.code 
WHERE t1.status    ='P'AND   t2.tasklist <>'y'