update a set a.qty=a.qty-b.qty from a,b where b.id in ('1','2') and a.pro=b.pro
大家好,在上面的UPDATE中,我用select * from a,b where b.id in ('1','2') and a.pro=b.pro查询是有两条记录,为什么执行UPDATA语句后,只更新了第一条记录的qty?
表如下:
表a:
id qty pro
1 5 书
表b:
id qty pro
1 2 书
2 3 书
执行update后:a.qty=3,为什么不是0?要怎样update才会是0?
大家好,在上面的UPDATE中,我用select * from a,b where b.id in ('1','2') and a.pro=b.pro查询是有两条记录,为什么执行UPDATA语句后,只更新了第一条记录的qty?
表如下:
表a:
id qty pro
1 5 书
表b:
id qty pro
1 2 书
2 3 书
执行update后:a.qty=3,为什么不是0?要怎样update才会是0?
set a.qty=a.qty-(select sum(b.qty) from a,b where b.id in ('1','2') and a.pro=b.pro)
set a.qty=a.qty-b.qty
from
a,
(select sum(b.qty) as qty from a,b where b.id in ('1','2') and a.pro=b.pro) b
where
a.pro=b.pro
update a set qty = qty - isnull((select sum(qty) from b where id in ('1','2') and pro=a.pro),0) from a
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int,[qty] int,[pro] varchar(2))
insert [a]
select 1,5,'书'
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[qty] int,[pro] varchar(2))
insert [b]
select 1,2,'书' union all
select 2,3,'书'update a
set a.qty=a.qty-b.qty
from
a,
(select a.pro,sum(b.qty) as qty from a,b where b.id in ('1','2') and a.pro=b.pro group by a.pro) b
where
a.pro=b.proselect * from a
--测试结果:
/*
id qty pro
----------- ----------- ----
1 0 书(1 行受影响)*/
from a,(select sum(qty)qty,pro from b where id in ('1','2') group by pro)b
where a.pro=b.pro