sql - 更新 ID 匹配的数据和用例以选择哪些数据

Question

我有两张 table

T1:
ID, Truck, Trailer
1   null   null
2   null   null

T2:
ID, Type, ResourceID 
1   R     111
1   F     222
1   D     333
2   R     444
2   F     555

我需要一个结果在哪里

ID, Truck, Trailer
1   111    222
2   444    555

如何更新 T1.Truck = T2.ResourceID当 T2.Type = R和 T1.Trailer = T2.ResourceID当 T2.Type = F哪里 T1.ID = T2.ID .

这是我到目前为止

UPDATE T1
    SET T1.Truck = CASE
    WHEN T2.Type = 'R' THEN T2.ResourceId
    ELSE T1.Truck
    END,
    T1.Trailer = CASE 
    WHEN T2.Type = 'F' THEN T2.ResourceId
                        ELSE T1.Trailer
                    END
    FROM T1 INNER JOIN (SELECT Id, Type, ResourceId
                             FROM T2) T2
                              ON T1.Id = T2.Id

这只会卡车，而不是拖车。

我错过了什么？

Answer 1

您当前更新查询的问题在于更新会将一列更新为空，因为它无法同时匹配两个条件。

如果你做了一个 select *结果将如下所示:

ID  Truck   Trailer Id  Type    ResourceId
1   NULL    NULL    1   R       111 -- this will set R = 111  and F = null
1   NULL    NULL    1   F       222 -- this will set F = 222  and R = null
1   NULL    NULL    1   D       333 -- this will set R = null and F = null
2   NULL    NULL    2   R       444 -- this will set R = 444  and F = null
2   NULL    NULL    2   F       555 -- this will set R = null and F = 555

在这里你可以看到当 Type匹配 R F 的更新将更新到 null等等。

一种解决方案是加入 T2表两次:

UPDATE T1
SET 
    T1.Truck = T2.ResourceId
    ,
    T1.Trailer = T3.ResourceId
FROM T1 
INNER JOIN (SELECT Id, ResourceId FROM T2 WHERE Type = 'R') T2 ON T1.Id = T2.Id
INNER JOIN (SELECT Id, ResourceId FROM T2 WHERE Type = 'F') T3 ON T1.Id = T3.Id

如果可能不总是两种类型( R ， F )然后使用 left join而不是 inner join并检查 null值。

编辑:多思考一下给出了这个查询:

UPDATE T1
SET 
    T1.Truck   = ISNULL(T.rValue, T1.Truck),
    T1.Trailer = ISNULL(T.fValue, T1.Trailer)
FROM T1 
INNER JOIN (
    SELECT 
       Id, 
       rValue = MAX(CASE WHEN Type = 'R' THEN ResourceId END),
       fValue = MAX(CASE WHEN Type = 'F' THEN ResourceId END)
    FROM T2 GROUP BY id
    ) T ON T1.Id = T.Id

附带说明:对也是表名的派生表使用别名可能会非常困惑，应该避免。