通过合并MSSQL中的另一个表来更新空值

Question

我有两个表格，例如：

表1：

Customer    employee
ASD_1234    WF001
ASD_1235    WF002
ASD_1236    WF003
ASD_1237    NULL
ASD_1238    NULL
ASD_1239    NULL
ASD_1240    WF004
ASD_1234    WF001
ASD_1236    WF003
ASD_1240    WF004

table2：

Customer    com_employee
ASD_1234    WF001
ASD_1235    WF002
ASD_1236    WF003
ASD_1237    WF005
ASD_1238    WF006
ASD_1239    WF007
ASD_1240    WF004

表2是由唯一的客户组成的元数据。我该如何实现？谢谢

Answer 1

您可以使用相关查询：

UPDATE table1
SET employee = (
    SELECT com_employee
    FROM table2
    WHERE table2.customer = table1.customer
)
WHERE employee IS NULL

Answer 2

您可以使用具有employee的无宿目条件的更新 - 加入语句：

UPDATE t1
SET    t1.employee = t2.com_employee
FROM   t1
JOIN   t2 ON t1.customer = t2.customer
WHERE  t1.employee IS NULL

Answer 3

我们可以在此处尝试使用可更新的CTE：

WITH cte AS (
    SELECT t1.employee, t2.com_employee
    FROM Table1 t1
    INNER JOIN Table2 t2
        ON t1.Customer = t2.Customer
)
UPDATE cte
SET employee = com_employee
WHERE employee IS NULL;