多个 id 的更新语句 [英] Update statement for multiple ids

问题描述

我有 3 个表，我需要通过计算其他两个表的数据来更新第 3 个表的列.

I have 3 tables, i need to update 3rd table's column by calculating data from other two tables.

update table3 set column3=
(
select t2.column3+t1.column3
from table2 t2 with (nolock) join table1 t1
on table2.id=t1.id
where table2.id= 100
)
where id= 100;

这个查询工作正常，它更新了第三个表列，但是如果我提供这样的 IN 运算符:

This query works fine, it updates the 3rd table column, however if i supply IN operators like this:

  update table3 set column3=
    (
    select t2.column3+t1.column3
    from table2 t2 with (nolock) join table1 t1
    on table2.id=t1.id
    where table2.id IN (100,101)
    )
    where id IN (100,101);

这失败了，我收到了这条消息

this fails and i get this message

子查询返回了 1 个以上的值.当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.声明已终止.

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

&我知道这是因为子查询返回超过 1 行，我该如何处理这种情况?任何提示/想法都会有所帮助.

& i know this is because subquery is returning more than 1 row, how can i handle this scenario? Any hint/thought will be helpful.

如何更新多个 ID?IE.ID 100 返回的选择查询值应根据第三个表中的 ID 100 进行更新与 ID 101 类似.

How do i update for multiple ids? ie. the select query value returned by ID 100 should be updated against ID 100 in 3rd table & similarly for ID 101.

另外，我需要做这样的总和 sum(t2.column3)- (t1.column3 + t1.column2)

Also, I need to do a sum like this sum(t2.column3)- (t1.column3 + t1.column2)

 update table3 set column3=
        (
        select  sum(t2.column3)- (t1.column3 + t1.column2)
        from table2 t2 with (nolock) join table1 t1
        on table2.id=t1.id
        where table2.id IN (100,101)
        )
        where id IN (100,101);

推荐答案

这是因为您试图将 column3 设置为返回的结果，而 SQL 期望它仅为一个值(标量).当您传递一个以上的返回值时，SQL 引擎会感到困惑(它应该使用哪一个?......它不假定遍历结果).所以，如果你想更新整个结果集，那么你需要从你的查询中创建一个子表并加入它.您的查询应该更像这样

This is because you are trying to set column3 to a returned result, and SQL expects that to be one value only (scalar). The SQL engine gets confused when you pass it more than one return value (which one should it use?...it does not assume to iterate through the results). So, if you want to update an entire result set, then you need to create a subtable from you query and join on that. Your query should look more like this

UPDATE Table3
SET Column3 = subtable.value
FROM Table3 
    JOIN (
        select t2.column3+t1.column3 as value, t1.id
        from table2 t2 with (nolock) join table1 t1
        on table2.id=t1.id
        where table2.id IN (100,101)
    ) AS subtable
    ON subtable.id = Table3.id
WHERE table3.id IN (100, 101)

在 table3.id 与其他 id 匹配的假设下，您也确实不需要内部 where table2.id IN ...

Under this assumption that table3.id matches the other id's, you also really do not need the inner where table2.id IN ...

这篇关于多个 id 的更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！