在 SQL Server 更新语句中使用相关子查询会产生意外结果
我正在向还没有主键列的表引入主键列。添加默认值为 0 的普通字段 Id (int) 后，我尝试使用以下更新语句为每条记录创建唯一值：
update t1
set t1.id = (select count(*) from mytable t2 where t2.id <> t1.id)
from mytable t1
我希望为每一行执行子查询因为我引用的是 t1。每次执行子查询时，计数都应该减一，但它不起作用。
结果是每条记录的Id仍然是0。我之前曾在其他 DBMS 上成功使用过它。我这里使用的是 SQL Server 2008。
如何为每条记录生成唯一值并更新 Id 字段？
I'm introducing a primary key column to a table that doesn't have one yet. After I have added a normal field Id (int) with a default value of 0 I tried using the following update statement to create unique values for each record:
update t1
set t1.id = (select count(*) from mytable t2 where t2.id <> t1.id)
from mytable t1
I would expect the subquery to be executed for each row because I'm referencing t1. Each time the subquery would be executed the count should be one less but it doesn't work.
The result is that Id is still 0 in every record. I have used this before on other DBMS with success. I'm using SQL Server 2008 here.
How do I generate unique values for each record and update the Id field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试解释为什么它没有按您的预期工作：
它被执行并且可以影响所有行。但是 UPDATE 语句是一条语句，并且它作为影响整个表（如果有 WHERE 子句则影响其中一部分）的一条语句执行。
您期望通过对每行子查询进行一次评估来执行
UPDATE。但它是首先对所有受影响的行进行评估的一条语句，然后更改（更新）这些行。 （DBMS 可能会以其他方式执行此操作，但结果应该就像它是这样做的）。当执行前所有行都具有相同的
0值时，这是该语句的正确且预期的行为。COUNT(*)为0。我的“疯狂”猜测是你在 MySQL 中使用过它。 （更正/更新：我的猜测是错误的，这种更新语法对 MySQL 无效，显然查询在 Firebird 中“正确”工作）。
UPDATE在该 DBMS 中不能以标准方式工作。正如您所知，它是逐行工作的，而不是整个表。此 DBMS 可以与
UPDATE一起正常工作。您可以编写一个不同的 Update 语句来获得所需的结果，或者更好的是，按照其他人的建议，使用自动生成的 IDENTITY 列。Trying to explain why it doesn't work as you expect:
It is executed and it can affect all rows. But an
UPDATEstetement is one statement and it is executed as one statement that affects a whole table (or a part of it if you have aWHEREclause).You are expecting the
UPDATEto be executed with one evaluation of the subquery per row. But it is one statement that is first evaluated - for all affected rows - and then the rows are changed (updated). (A DBMS may do it otherwise but the result should be nonetheless as if it was doing it this way).That's the correct and expected behaviour of this statement when all rows have the same
0value before execution. TheCOUNT(*)is0.My "wild" guess is that you have used it in MySQL. (Correction/Update: my guess was wrong, this syntax for Update is not valid for MySQL, apparently the query was working "correctly" in Firebird). The
UPDATEdoes not work in the standard way in that DBMS. It works - as you have learned - row by row, not with the full table.This DBMS works correctly with
UPDATE. You can write a different Update statement that would have the wanted results or, even better, use an autogeneratedIDENTITYcolumn, as others have advised.SQL 使用 ID 不等于 0 的记录数更新每一行。由于所有行 ID 都等于 0，因此不存在不等于 0 的行，因此不会更新任何内容。
尝试在此处查看此答案：
向现有列添加身份
The SQL is updating every row with the number of records where the ID doesn't equal 0. As all the rows ID equal 0 then there are no rows that are not equal to 0, and hence nothing gets updated.
Try looking at this answer here:
Adding an identity to an existing column