select a.fromdeptno,b.partno,C.description,b.qty,b.cost,c.parttype,c.brand
from business a,bitem b,part c
where a.bid = b.bid and b.partno = c.partno and a.fromdeptno = 'V001' and a.btype='17' and c.parttype in ('C2','B9') and c.brand = 'X'
order by c.description根据上面的查询将 产生171条记录,我要按照上面的条件把 a.fromdeptno 的值是'V001'的记录改为 'V009'
我是这样编的,但只更新了10条记录,不知问题出现在那?如下,请高手指点。
update business
set fromdeptno = 'V009'
from business as a inner join bitem b on (a.bid = b.bid) ,part c
where fromdeptno = 'V001' and btype='17' and b.partno = c.partno and c.parttype in ('C2','B9') and c.brand = 'X'
from business a,bitem b,part c
where a.bid = b.bid and b.partno = c.partno and a.fromdeptno = 'V001' and a.btype='17' and c.parttype in ('C2','B9') and c.brand = 'X'
order by c.description根据上面的查询将 产生171条记录,我要按照上面的条件把 a.fromdeptno 的值是'V001'的记录改为 'V009'
我是这样编的,但只更新了10条记录,不知问题出现在那?如下,请高手指点。
update business
set fromdeptno = 'V009'
from business as a inner join bitem b on (a.bid = b.bid) ,part c
where fromdeptno = 'V001' and btype='17' and b.partno = c.partno and c.parttype in ('C2','B9') and c.brand = 'X'
如:
business 中 fromdeptno = 'V001' 并满足其他连接条件的记录有 10 条,但
bitem 中,满足 a.bid = b.bid 的记录对应每个 a.bid 中有 10 条,那连接起来的查询数据就是100条了.
update a set a.fromdeptno = 'V009'
from business a,bitem b,part c
where a.bid = b.bid and b.partno = c.partno and a.fromdeptno = 'V001' and a.btype='17'
and c.parttype in ('C2','B9') and c.brand = 'X'
select distinct a.fromdeptno,b.partno,C.description,b.qty,b.cost,c.parttype,c.brand
from business a,bitem b,part c
where a.bid = b.bid and b.partno = c.partno and a.fromdeptno = 'V001' and a.btype='17' and c.parttype in ('C2','B9') and c.brand = 'X'
order by c.description