Question

SQL 语句 UPDATE带嵌套子查询的问题

create table AAA( mc varchar(10), s1 int ) create table BBB( mc varchar(10), s1 int ) insert AAA values('A','997')insert AAA values('B','1234') insert BBB values('A','105')insert BBB values('A','213')insert BBB values('B','116')insert BBB values('B','211')insert BBB values('B','303')更新A表里S1 值 ，A表里S1值-去B表里值 update AAA set AAA.s1=AAA.s1-BBB.s1where  BBB.s1 in (select sum(BBB.s1) from BBB group by mc) 我到底错在了哪  望高手指教

Answer 1

create table AAA( mc varchar(10), s1 int )
insert AAA values('A','997')
insert AAA values('B','1234')

create table BBB( mc varchar(10), s1 int )
insert BBB values('A','105')
insert BBB values('A','213')
insert BBB values('B','116')
insert BBB values('B','211')
insert BBB values('B','303')

-- 更新A表里S1值 ，A表里S1值-去B表里值
-- ? B表里S1值之合?
update AAA set
s1 = s1 - isnul((select sum(s1) from BBB where BBB.mc=AAA.mc),0)
-- next for M$SQL:
update AAA set
s1 = s1 - B.s1
from AAA
join (select mc, sum(s1) as s1 from BBB group by mc) B
on B.mc = AAA.mc

Answer 2

我其实对你的这个问题有几点不明白的,
1.你的S1是int类型的,但是插入的时候是'999'这样的字符串.
2.update语句的逻辑是什麽,就是你想要得到一个什麼样的结果.
where BBB.s1 in (select sum(BBB.s1) from BBB group by mc)这句的逻辑有问题!
3.update语句如果有where条件,应该这样写
update a set A.S1=AAA.s1-BBB.s1
FROM AAA a
where BBB.s1 in (select sum(BBB.s1) from BBB group by mc)
希望可以帮到您.

Answer 3

看了你的意思，语句应该这样写

update aaa set s1=a.s1-b.s1
from aaa a
left join (select mc,sum(BBB.s1) s1 from BBB group by mc) b on a.mc=b.mc

Answer 4

你的更新语句写法不对，你改为这样试试：
UPDATE AAA
SET AAA.S1 = AAA.S1 - B.S1
FROM (SELECT SUM(S1)
FROM BBB
GROUP BY MC
) B
WHERE AAA.MC = B.MC