SQLTeam.com | Weblogs | Forums

SQL function

I have 2 tables

Employee

Status
4
5
6

Table 2
Lookup

Status ,new_id
4,1001
5,1002
6,1003

What I want to do is to create a function that updates Employee table using the new_id in lookup table.

Example employee new value for
Status 4 will be 1001
Based off the the value in lookup table
5 = 1002 etc .....

UPDATE E
SET Status = new_id
FROM dbo.Employee E
INNER JOIN dbo.Lookup L ON L.Status = E.Status