CODEWITHSUNDEEP
sqlsql-server
user1326379
user1326379

Reputation: 170

Update Values in One Table where value of one column is not equal

I have 2 tables like below:

Table Employee1:

Emp Id      Name        Department
0001        Jack        Accounts
0002        Peter       Sales
0003        Beck        Sales
0004        Nancy       Marketing
0005        Parker      HR

Table Employee2:

Emp Id      Name        Department
0001        Jack        HR
0002        Peter       Marketing
0004        Nancy       Sales
0005        Parker      Accounts

I would like to have a SQL Server script that will update the table Employee1 to:

Emp Id      Name        Department
0001        Jack        HR
0002        Peter       Marketing
0003        Beck        Sales
0004        Nancy       Sales
0005        Parker      Accounts

Any pointers?

Upvotes: 0

Views: 62

Answers (4)

hi olaf
hi olaf

Reputation: 227

You want the MERGE SQL command to update lines different and insert new ones int he same command:

MERGE INTO Employee1 WITH (HOLDLOCK) AS target
USING Employee2 AS source
    ON target.[Emp Id] = source.[Emp Id]
WHEN MATCHED THEN 
    UPDATE SET target.Department = source.Department
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Emp Id], Name, Department)
    VALUES (source.[Emp Id], source.Name, source.Department);

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this

Update T1
SET T1 = CASE WHEN T1.Department <> T2.Department  
              THEN T2.Department 
              ELSE T1.Department 
          END
FROM Employee1 T1
INNER JOIN Employee2 T2  ON T1.[Emp Id] = T2.[Emp Id]

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

We can use an update join here:

UPDATE a
SET Department = b.Department
FROM Employee1 a
INNER JOIN Employee2 b
    ON a.[Emp Id] = b.[Emp Id]
WHERE
    a.Department <> b.Department

Upvotes: 1

Valerica
Valerica

Reputation: 1630

You could try UPDATE with JOIN

UPDATE E1
    SET E1.Department = E2.Department
FROM Employee1 E1
INNER JOIN Employee2 E2
    ON E1.[Emp Id] = E2.[Emp Id]

Upvotes: 2

Related Questions