SQL UPDATE 从多行到单行 [英] SQL UPDATE from multiple rows to single row

问题描述

这说明了问题:

CREATE TABLE Table_A (id int, value char)
INSERT INTO Table_A VALUES (1, 'A')
INSERT INTO Table_A VALUES (2, 'B')
CREATE TABLE Table_B (id int, value char)
INSERT INTO Table_B VALUES (1, 'C')
INSERT INTO Table_B VALUES (1, 'D')

如果你跑了

UPDATE a SET a.value = (SELECT b.value FROM Table_B b WHERE a.id = b.id)
FROM Table_A a, Table_B b WHERE a.id = b.id

您收到一条错误消息

子查询返回了 1 个以上的值.这在以下情况下是不允许的子查询跟随 =, !=, <, <= , >, >= 或当子查询用作一个表达式.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

但是如果你运行这个

UPDATE a SET a.value = b.value
FROM Table_A a, Table_B b WHERE a.id = b.id

没有抛出错误并且您更新了行，这是为什么?

No error is thrown and you get the row updated, why is that?

抱歉，你们似乎专注于解释为什么第一个查询会出错，但我认为这是显而易见的，对我来说这是一个理想的结果(因为将 id 1 的 Table_A 的值设置为 id 1 的 Table_B 的值是当 Table_B 中有多个值且 id 为 1) 时未定义

Sorry guys, you seem to focusing on explaining why the first query gives error, but I think that is obvious and to me that is a desire result (because setting value of Table_A for id 1 to value of Table_B with id 1 is undefined when there are multiple values in Table_B with id 1)

我的问题实际上是在问为什么第二个查询没有给您一个错误，这给我带来了麻烦(即，如果我有多个具有相同 ID 但不同值的行，我希望它中断)

My question is actually asking why the second query does not give you an error, which is causing trouble to me (i.e. I want it to break if I have more than one row with the same id but different values)

推荐答案

您遇到该错误是因为您在设置新值时正在使用子查询，并且子查询返回 1 个以上的结果.

You got that error because you are using subquery when you set a new value and the subquery return more than 1 result.

SET a.value = (SELECT b.value FROM Table_B b WHERE a.id = b.id)

更新一个id=1的值会报错，因为表b中有两条id=1的记录.

It will error when update a value with id = 1, because there is two record that have id = 1 in table b.

所以你的查询看起来像这样(这只是为了说明，当然会导致错误)

So your query will look like this (this is only for illustration and of course will cause an error)

UPDATE a SET a.value = ('C', 'D') 
FROM Table_A a, Table_B b WHERE a.id = b.id

当您使用此查询时

UPDATE a SET a.value = b.value
FROM Table_A a, Table_B b WHERE a.id = b.id

您使用 id 字段将表 a 与表 b 连接，因此结果为

You are join the table a with table b using id field, so the result is

a.id => a.value => b.value : 1 A C

a.id => a.value => b.value : 1 A D

没有 id = 2 的条目记录，因为表 b 中没有匹配的记录.

No entry record for id = 2 because there is no matching record in table b.

因此您的更新查询将如下所示

So your update query will looks like this

UPDATE a SET a.value = 'C'
FROM Table_A a, Table_B b WHERE a.id = 1


UPDATE a SET a.value = 'D'
FROM Table_A a, Table_B b WHERE a.id = 1

这篇关于SQL UPDATE 从多行到单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！