更新一个表中的值，其中一列的值不相等

I have 2 tables like below:

我有2个表，如下所示：

Table Employee1:

表Employee1：

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

Table Employee2:

表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:

我想有一个SQL Server脚本，将表Employee1更新为：

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

Any pointers?

有什么指针吗？

4 个解决方案

#1

You could try UPDATE with JOIN

您可以尝试使用JOIN进行更新

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

#2

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

#3

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]

#4

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

您希望MERGE SQL命令更新不同的行并在同一命令中插入新的行：

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);

#1