table1 :
code , quantity
001 10
002 50
table2a:
id, ccode
1 A
2 B
table2b:
id , code ,quantity
1 001 20
1 002 80
2 001 30
以下更新语句出错,应该怎样用一条语句写?
update x
set x.quantity=table1.quantity
from table1,(select code,quantity from table2a left join table2b on table2a.id=table2b.id where table2a.ccode='A') x
code , quantity
001 10
002 50
table2a:
id, ccode
1 A
2 B
table2b:
id , code ,quantity
1 001 20
1 002 80
2 001 30
以下更新语句出错,应该怎样用一条语句写?
update x
set x.quantity=table1.quantity
from table1,(select code,quantity from table2a left join table2b on table2a.id=table2b.id where table2a.ccode='A') x
set table2b.quantity=table1.quantity
from tale2b,table1
where table2b.id in (select id from table2a where ccode='A')
可以不?
set t2.quantity=t1.quantity
from tale2b t2,table1 t1
where t2.id in (select id from table2a where ccode='A')
and t1.id = t2.id 关键你的table1 与 table2 的关联关系在哪?
set table2b.quantity=table1.quantity
from tale2b,table1
where table2b.id in (select id from table2a where ccode='A') and table2b.code=table1.code
写漏了,哈哈