sql - 使用 LEFT JOIN 时，此脚本如何更新表？

Question

此脚本中的结果是正确的，但我似乎不明白为什么 BetaStatus 列仍然为“NOK”

关于“Beta”，第一行 (Beta = NOK) 会将 @Summary.BetaStatus 更新为 NOK。但后来我认为 @testTable 中的最后两行会将 BetaStatus 从 NOK 更新回 OK。我只是想确定“NOK”实际上是要处理的最后一行，因此也是最后一个值，这并不是巧合。

declare @testTable table 
(
    id int,
    Pgroup varchar(10),
    Pstatus varchar(3)
)
insert into @testTable select 3, 'Alpha', 'OK'
insert into @testTable select 3, 'Beta',  'NOK'
insert into @testTable select 3, 'Gamma', 'OK'
insert into @testTable select 3, 'Beta',  'OK'
insert into @testTable select 3, 'Beta',  'OK'

declare @Summary table
(
    id int,
    AlphaStatus varchar(3),
    BetaStatus varchar(3),
    GammaStatus varchar(3)
)
insert into @Summary (id) select 3

update @Summary 
set
    AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus),
    BetaStatus = ISNULL(rB.Pstatus,  BetaStatus),
    GammaStatus = ISNULL(rG.Pstatus, GammaStatus)
from @Summary t
left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha'
left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta'
left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma'

select * from @summary

我问的原因是，对于每个 id，AlphaStatus、BetaStatus、GammaStatus 如果之前是“NOK”，则不应改回“OK”。一旦更新为“NOK”，无论接下来发生什么，它都会保持这种状态。

另一种方法是使用“OK”值更新@Summary，然后使用“NOK”进行另一次更新。这样我就知道“NOK”不会被替换。但如果这有效，那么我宁愿使用它。

第二个问题，如果我使用 INNER JOIN，为什么 UPDATE 不能正常工作？

谢谢。

Answer 1

让我们看看返回的是 select 而不是 update

select 
     AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus),
     BetaStatus  = ISNULL(rB.Pstatus,  BetaStatus),
     GammaStatus = ISNULL(rG.Pstatus, GammaStatus)
from @Summary t
left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha'
left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta'
left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma'

结果:

AlphaStatus     BetaStatus  GammaStatus
OK              NOK         OK
OK              OK          OK
OK              OK          OK

现在您尝试执行UPDATE

update @Summary 
set
    AlphaStatus = ISNULL(rA.Pstatus, AlphaStatus),
    BetaStatus  = ISNULL(rB.Pstatus,  BetaStatus),
    GammaStatus = ISNULL(rG.Pstatus, GammaStatus)
from @Summary t
left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha'
left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta'
left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma'

更新表@Summary后包含:

id  AlphaStatus     BetaStatus  GammaStatus
3   OK              NOK         OK

我想你想得到:

id  AlphaStatus     BetaStatus  GammaStatus
3   OK              OK      OK

但是UPDATE不会那样工作，当它们是多个匹配时，结果可能不一致，并且它部分基于表排序或实际执行计划。

另请参阅:Let's deprecate UPDATE FROM! by Hugo Kornelis

Correctness? Bah, who cares?

Well, most do. That’s why we test.

If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…

也看到了这种不一致，而不是表变量使用表并创建聚集索引:

<强> SqlFiddleDemo

CREATE TABLE testTable(id int,
    Pgroup varchar(10),
    Pstatus varchar(3));

CREATE CLUSTERED INDEX clx_name
ON  testTable(PStatus DESC);

/* or */
CREATE CLUSTERED INDEX clx_name
ON  testTable(PStatus ASC);  

例如，如果您使用 MERGE:

;WITH cte as
(SELECT 
   ra.id
   ,AlphaStatus = rA.Pstatus
   ,BetaStatus = rB.Pstatus
   ,GammaStatus = rG.Pstatus
from @Summary t
left join @testTable rA on rA.id = t.ID AND rA.Pgroup = 'Alpha'
left join @testTable rB on rB.id = t.ID AND rB.Pgroup = 'Beta'
left join @testTable rG on rG.id = t.ID AND rG.Pgroup = 'Gamma'
)
MERGE @Summary AS TGT
USING (SELECT * FROM cte ) AS  SRC
   ON TGT.id = SRC.id
WHEN MATCHED THEN
   UPDATE
   SET 
    AlphaStatus = ISNULL(src.AlphaStatus, tgt.AlphaStatus),
    BetaStatus  = ISNULL(src.BetaStatus,  tgt.BetaStatus),
    GammaStatus = ISNULL(src.GammaStatus, tgt.GammaStatus);

您将收到明确的错误消息，表明这是不允许的:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.