使用 LEFT JOIN 时，此脚本如何更新表? [英] How is this script updating table when using LEFT JOINs?

问题描述

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

The result in this script is correct, but I don't seem to understand why column BetaStatus remains 'NOK'

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

Regarding 'Beta', the first row (Beta = NOK) would update @Summary.BetaStatus to NOK. But then I thought that the last two rows in @testTable would update BetaStatus from NOK back to OK. I just want to be certain that it's not a coincidence that the 'NOK' is actually the last row to be processed, and hence the value.

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"，无论接下来发生什么，它都会保持这种状态.

The reason I ask is that is that, for every id, AlphaStatus, BetaStatus, GammaStatus should not be changed back to 'OK' if it was previously 'NOK'. Once it's updated to 'NOK' it remains that way regardless of what comes next.

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

The alternative was to update @Summary with the 'OK' values, and then do another update with 'NOK'. That way I know that the 'NOK' will not be replaced. But if this works, then I'd rather use this.

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

And as a second question, why doesn't the UPDATE work correctly if I use INNER JOIN?

谢谢.

推荐答案

让我们看看什么返回 select 而不是 update

Let's see what return select instead of 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 后包含:

After update table @Summary contains:

id  AlphaStatus     BetaStatus  GammaStatus
3   OK              NOK         OK

我想你想得到:

id  AlphaStatus     BetaStatus  GammaStatus
3   OK              OK      OK

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

But UPDATE doesn't work that way, when they are multiple matches the result may be inconsistent and it is partially based on the table ordering or actual execution plan.

另见:让我们弃用 UPDATE FROM！作者:雨果·科内利斯

正确吗?呸，谁在乎?

嗯，大多数都这样.这就是我们进行测试的原因.

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

如果我在 SELECT 查询中弄乱了连接条件，导致太多行从第二个表匹配，我一测试就会看到它，因为我获得比预期更多的行.如果我弄乱了子查询条件在 ANSI 标准 UPDATE 查询中以类似的方式，我什至看到它更快，因为如果子查询，SQL Server 将返回错误返回多个值.但是使用专有的 UPDATE FROM语法，我可以弄乱连接并且永远不会注意到 – SQL Server 会如果匹配更多，则愉快地一遍又一遍地更新同一行连接表中不止一行，只有最后一个的结果那些更新会一直存在. 并且无法知道是哪一行会，因为这取决于发生的查询执行计划被选中.最坏的情况是执行计划恰好在所有测试期间产生预期结果在单处理器开发服务器上——然后，在部署到四路双核生产服务器，我们宝贵的数据突然轰动了粉丝......

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…

太看到这种不一致而不是表变量使用表并创建聚簇索引:

Too see this inconsistency instead of table variable use table and create clustered indexes:

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:

If you use MERGE for instance:

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

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

You will get clear error message that this is not allowed:

MERGE 语句多次尝试更新或删除同一行.当目标行匹配多个源行时会发生这种情况.MERGE 语句不能多次更新/删除目标表的同一行.细化 ON 子句以确保目标行最多匹配一个源行，或者使用 GROUP BY 子句对源行进行分组.

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.

这篇关于使用 LEFT JOIN 时，此脚本如何更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！