問題描述
我使用的是 SQL Server 2008, R2.有一個主表(表 A)，我正在嘗試使用臨時表(表 B)中的值更新它.
I am using SQL Server 2008, R2. Have a master table (table A), and am trying to update it with values from a temp table (Table B).
SQL Server 出錯，說子查詢返回了多個值，但是我不明白這是怎么可能的，因為子查詢返回的值是表 B 的主鍵.
SQL Server is erroring out, saying that the subquery returned more than one value, however I don't see how this is possible since the value returned by the subquery is the primary key of Table B.
這是查詢:
UPDATE TableA
SET TableA.field = (SELECT TableB.field
FROM TableA
INNER JOIN TableB ON TableA.key = TableB.key)
非常感謝任何幫助，像往常一樣！
Any assistance greatly appreciated, as usual!
推薦答案
您的子查詢根本不相關.子查詢中的標識符TableA.key"指的是子查詢的 FROM 子句中的 TableA，而不是更新的目標表(恰好也是 TableA).您不想使用兩表連接的結果集更新 TableA.field.你只是想要這個:
Your subquery is not correlated at all. The identifier "TableA.key" in the subquery refers to the TableA in the subquery's FROM clause, not the target table of the update (which happens also to be TableA). You don't want to update TableA.field with the result set of a two-table join. You simply want this:
UPDATE TableA
SET TableA.field = (SELECT TableB.field
FROM TableB
WHERE TableA.key = TableB.key)
這篇關于SQL 更新查詢和“子查詢返回多個值"的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！