user3331421 user3331421 - 4 months ago 7
SQL Question

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'
FROM Employee e
WHERE e.Code IN
INNER JOIN (SELECT EmployeeCode FROM Time WHERE status = 'P') permanent
ON permanent.Employeecode = e.Code
AND permanent.Employeecode NOT IN
(SELECT EmployeeCode FROM Time t WHERE t.EmployeeCode=permanent.Employeecode and t.tasklist<>'y' )


My code is updating only one row. Can anyone please help?

Employee table :
Code Name Hours OnTrack




1E SCOTT 32

2E LISA 32

3E MARK 32

Time table is
Code Employeecode Status workingHours TaskList




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

The result what expected is in employee table, OnTrack status should be updated if all its employee code has 'Y' in Time table as below:

Code Name Hours OnTrack

1E SCOTT 24 N

2E LISA 12 Y

3E MARK 0 Y

Answer

(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 
INNER JOIN TIME t1 ON t1.EmployeeCode  =   e.code 
INNER JOIN TIME 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 
INNER JOIN TIME t1 ON t1.EmployeeCode   = e.code 
INNER JOIN TIME t2 ON t2.EmployeeCode  <> e.code 
WHERE t1.status    = 'P' 
AND   t2.tasklist <> 'y'
Comments