Hey there I have two tables. Grade table with data (gID) that I need to transfer to the Employee table (GradeID).
Employee
EmployeeID | Name | mgr | Job | Salary| Comm | HireDate | dID| GradeID
1001 | Susan Adams | 1004| Locksmith| 60000| 20000| 2005-04-12| 1 | NULL
1002 | Ricky Jones | 1004| Writer| 85000 | 15000| 2020-01-18| 1 | NULL
1003 | Beatrice | 1006| Editor| 89000 | NULL | 2002-03-07| 2 | NULL
GradeID| MinSal| MaxSal| Holiday
A | NULL | 60000 | 40
B | 60000 | 80000 | 40
C | 90000 | 100000| 40
D | 200000| 120000| 45
E | 320000| NULL | 55
This is what I have so far
UPDATE EMPLOYEE
SET GradeID = 'A'
WHERE Salary between (SELECT COALESCE(MAX(MinSAL),0) From GRADE WHere gID = 'A') and (SELECT Max (MaxSal) From GRADE where gID = 'A')
UPDATE EMPLOYEE
SET GradeID = 'B'
WHERE Salary between (SELECT MAX(MinSal) From GRADE WHere gID = 'B') and (SELECT Max (MaxSal) From GRADE where gID = 'B')
UPDATE EMPLOYEE
SET GradeID = 'C'
WHERE Salary between (SELECT MAX(MinSal) From GRADE WHere gID = 'C') and (SELECT Max (MaxSal) From GRADE where gID = 'C')
UPDATE EMPLOYEE
SET GradeID = 'D'
WHERE Salary between (SELECT MAX(MinSal) From GRADE WHere gID = 'D') and (SELECT Max (MaxSal) From GRADE where gID = 'D')
UPDATE EMPLOYEE
SET GradeID = 'E'
WHERE Salary between (SELECT MAX(MinSal) From GRADE WHere gID = 'E') and (SELECT COALESCE(MAX(MaxSAL),1000000000000000) From GRADE WHere gID = 'E')
But I need a less cluncky single query that can populate the correct grade in the Employee table based off their salary. Thanks for your help.
Try using an update join:
UPDATE e
SET e.GradeID = g.gID
FROM Employee e
INNER JOIN Grade g
ON (e.Salary > g.MinSal OR g.MinSal IS NULL) AND
(e.Salary <= g.MaxSal OR g.MaxSal IS NULL);