表A 有 id , rate 两列
表B 有id,value,khvalue 三列A,B两表的id有一部分是相同的当B.id=A.id时,把对应的B.khvalue的值更新为对应的B.value*A.rate我写的语句是update B set khvalue=B.value*A.rate where B.id=A.id 执行报错:“A.id” 标识符无效请高手指教,sql语句改怎么写
表B 有id,value,khvalue 三列A,B两表的id有一部分是相同的当B.id=A.id时,把对应的B.khvalue的值更新为对应的B.value*A.rate我写的语句是update B set khvalue=B.value*A.rate where B.id=A.id 执行报错:“A.id” 标识符无效请高手指教,sql语句改怎么写
set a.value = (select a.value*b.rate
from A b
where a.id = b.id);
楼主试试上面这个呢
UPDATE b
SET b.khvalue =
(SELECT b.value * A.rate FROM a WHERE a.id = b.id)
WHERE EXISTS (SELECT 1 FROM a WHERE a.id = b.id);
--2、使用merge
MERGE INTO b
USING a
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET b.khvalue = b.value * a.rate;
update tableb b set b.khvalue=b.value*(select a.rate from tablea a where a.id=b.id)
where exists (select * from tablea c where c.id=b.id)
merge into b using a on(b.id=a.id)
when matched then update set bkhvalue=b.value*a.rate
USING a
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET b.khvalue = b.value * a.rate;