在 FROM/JOIN 子句中未提及更新表的 UPDATE 语句中会发生什么? [英] What happens in an UPDATE statement in which the updated table isn't mentioned in the FROM/JOIN clauses?

问题描述

我打算在 SQL Server 数据库表上运行以下 UPDATE 语句:

I intended to run the following UPDATE statement on a SQL Server database table:

UPDATE TABLE_A
SET COL_1=B.COL_1
FROM TABLE_A A
INNER JOIN TABLE_B B
   ON A.KEY_1=B.KEY_1
WHERE B.COL_2 IS NOT NULL
  AND A.COL_1=91216599

我错误地运行了以下语句:

By mistake, I ran the following statement instead:

UPDATE TABLE_A
SET COL_1=B.COL_1
FROM TABLE_A_COPY A
INNER JOIN TABLE_B B
   ON A.KEY_1=B.KEY_1
WHERE B.COL_2 is not NULL
  AND A.COL_1=91216599

请注意，在第二个语句(错误的语句)中，FROM 子句指定了表 TABLE_A_COPY 而不是 TABLE_A.两个表具有完全相同的架构(即相同的列)和相同的数据(在执行任何 UPDATE 之前，即).

Notice that in this second statement (wrong one), the FROM clause specifies table TABLE_A_COPY instead of TABLE_A. Both tables have exactly the same schema (i.e., same columns) and the same data (before any UPDATE is executed, that is).

TABLE_A 和 TABLE_A_COPY 都有大约 1 亿条记录，更新影响大约 500,000 条记录.第二个语句(错误的)运行了几个小时并失败，而第一个语句(正确的)运行了 40 秒并成功.

Both TABLE_A and TABLE_A_COPY have about 100 million records and the update affects about 500,000 records. The second statement (the wrong one) runs for several hours and fails while the 1st statement (the correct one) runs for 40 seconds and succeeds.

显然，这两个语句在语法上都是正确的，但我不确定我要求 SQL Server 对第一个语句做什么.

Clearly, both statements are syntactically correct, but I am not sure what exactly I asked SQL Server to do with the first statement.

我的问题是:

1. SQL Server 试图在第二个语句中做什么? 由于我的错误，我没有指定从 TABLE_A 到 的记录之间的链接TABLE_A_COPY，那么它是否试图在两者之间进行 CROSS JOIN，然后将 TABLE_A 中的每条记录更新无数次?

1. What SQL Server was trying to do in the second statement? With my mistake I didn't specify the linkage between records from TABLE_A to TABLE_A_COPY, so was it trying to do a CROSS JOIN between the two, and then update each record in TABLE_A a gazillion times?

如果要问的问题不是太宽泛，那么 UPDATE 语句的有效场景是什么，其中 中未提及正在更新的表FROM/JOIN 子句.为什么会有人这样做?为什么 SQL Server 甚至会允许这样做?

If it isn't too broad a question to ask, what would be a valid scenario for such an UPDATE statement in which the table being updated is not mentioned in the FROM/JOIN clauses. Why would anyone do that? Why would SQL Server even allow that?

<小时>

^{我确实尝试寻找问题的答案，但 Google 似乎认为我在询问 UPDATE FROM 语法.}

推荐答案

1) TABLE_A 和 TABLE_A_COPY 之间没有连接，所以你会得到 CROSS JOIN 并大量更新同一行.如果涉及并行执行，结果可能是不确定的:

1) There is no connection between TABLE_A and TABLE_A_COPY so you will get CROSS JOIN and massive update the same row. Result can be non-deterministic if parallel execution is involed:

LiveDemo

CREATE TABLE #TABLE_A(KEY_1 INT PRIMARY KEY,COL_1 INT);

CREATE TABLE #TABLE_A_COPY(KEY_1 INT PRIMARY KEY,COL_1 INT);

CREATE TABLE #TABLE_B(KEY_1 INT PRIMARY KEY, COL_1 INT, COL_2 INT);

INSERT INTO #TABLE_A VALUES (1,91216599),(2,91216599),(3,91216599),
                             (4,91216599),(5,91216599),(6,6);

INSERT INTO #TABLE_A_COPY VALUES (1,91216599),(2,91216599),(3,91216599),
                                 (4,91216599),(5,91216599),(6,6);    

INSERT INTO #TABLE_B VALUES (1,10,10),(2,20,20), (3,30,30);

/*
UPDATE #TABLE_A
SET COL_1=B.COL_1
--SELECT *
FROM #TABLE_A A
INNER JOIN #TABLE_B B
   ON A.KEY_1=B.KEY_1
WHERE B.COL_2 IS NOT NULL
  AND A.COL_1=91216599;
*/

UPDATE #TABLE_A
SET COL_1=B.COL_1
FROM #TABLE_A_COPY A
INNER JOIN #TABLE_B B
   ON A.KEY_1=B.KEY_1
WHERE B.COL_2 is not NULL
  AND A.COL_1=91216599

SELECT *
FROM #TABLE_A;

检查上面的代码 TABLE_A 记录与 KEY_1 = 6 的变化.

Check in above code how TABLE_A record with KEY_1 = 6 changed.

2)SQL Server UPDATE FROM/DELETE FROM 语法比ANSI标准宽泛得多，你遇到的问题可以简化为多次更新同一行.使用 UPDATE 您不会收到任何错误或警告:

2) SQL Server UPDATE FROM/DELETE FROM syntax is much more broad than ANSI standard, the problem you encounter can be reduced to multiple update the same row. With UPDATE you don't get any error or warning:

来自 让我们弃用 UPDATEFROM! 和 弃用 UPDATE FROM 和 DELETE 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…

如果你使用例如 MERGE 你会得到错误指示:

If you use for example MERGE you will get error indicating:

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.

所以你需要更加小心并检查你的代码.我也希望得到错误，但正如您在连接链接中看到的那样，这不会发生.

避免这种情况的一种方法是使用 UPDATE alias，这样您就可以确保使用参与 FROM JOIN 的表并且不涉及其他表.:

One way to avoid this is to use UPDATE alias so you are sure you use tables that take part in FROM JOIN and no other tables are involved.:

UPDATE A
SET COL_1=B.COL_1
FROM #TABLE_A A
INNER JOIN #TABLE_B B
   ON A.KEY_1=B.KEY_1
WHERE B.COL_2 IS NOT NULL
  AND A.COL_1=91216599;

这篇关于在 FROM/JOIN 子句中未提及更新表的 UPDATE 语句中会发生什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！