Question

我有3个表格,需要通过计算其他两个表格的数据来更新第3个表。

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;

但是,如果要向本国经营者提供此类服务,则会更新第3栏:

  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);

这失败了,一收到这一信息

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个以上,如何处理这种情况? 任何 h/ough都将是有益的。

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.

此外,我需要作一等(t2.coumn3)-(t1.coumn3 + t1.coumn2)

 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);

Answer 1

这是因为你试图将<条码>coln3设定为一种回报结果,而Kingk则认为只有一种价值(尺度)。当你通过一个以上的回报价值(应当使用这一价值? ......它不会通过结果来推断出它)。因此,如果你想要更新整个成果,那么你需要从你问询中设立一个分局,并加入。你的问询更像这样看待。

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)

根据这一假设,表3.id与另一个脂重相匹配,你也确实不需要在表2.id内插入<代码>。

Answer 2

页: 1 引证:

UPDATE t3
SET column3 = t2.column3+t1.column3
FROM table3 t3
INNER JOIN table2 t2 WITH(NOLOCK) 
ON t3.id = t2.id
INNER JOIN table1 t1
ON t3.id=t1.id
WHERE t3.id IN (100,101)

友情链接