CODEWITHSUNDEEP
sqlsql-server-2008-r2
sameerag
sameerag

Reputation: 37

Update the id with some field id in another table

How to Update all employee departmentID who belongs to department code 500 to department code 503

**tHREmployee

> EmployeeId #.......Employeee.........DepartmentID
...101...............Ajith.............101
...102...............Arathy ...........Null
...103...............Arurna............102
...104...............Ambily............101
...105...............Anjaly............Null
...106...............Babitha...........103

**tHRDepartment

 DepartmentID #.............Code
 101........................500
 102........................501
 103........................502
 105........................503
 ..

Upvotes: 0

Views: 42

Answers (3)

Milen
Milen

Reputation: 8877

What about this:

 Declare @NewDepID int
 Select   @NewDepID = DepartmentID from Departments Where DepartmentCode = 503

 update tHREmployee 
 Set DepartmentID = @NewDepID
 Where DepartmentID in (Select DepartmentID from Departments Where DepartmentCode = 500)

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

DECLARE @CodeFrom AS INT
DECLARE @CodeTo AS INT

SET    @CodeFrom = 500 
SET    @CodeTo= 503

UPDATE tHREmployee
SET    DepartmentID = (
                          SELECT DepartmentID 
                          FROM   tHRDepartment 
                          WHERE  Code = @CodeTo
                      )
FROM   tHREmployee E
       JOIN tHRDepartment D
       ON E.DepartmentID  = D.DepartmentID 
WHERE  D.Code = @CodeFrom

Upvotes: 1

Laurence Frost
Laurence Frost

Reputation: 2993

Update tHREmployee SET DepartmentID = 105 WHERE DepartmentID = 101;

Upvotes: 0

Related Questions