SQL update 的更新值来源于另外一个表,如何实现?
2022-12-14 · 百度认证:北京惠企网络技术有限公司官方账号
1、创建两个测试表,
createtabletest_up_a(idnumber,valuevarchar2(100));
createtabletest_up_b(idnumber,valuevarchar2(100));
2、分别往两个表中插入数据;
insertintotest_up_avalues(1,'A1');
insertintotest_up_avalues(2,'A2');
insertintotest_up_avalues(3,'A3');
insertintotest_up_avalues(4,'A4');
insertintotest_up_avalues(5,'A5');
insertintotest_up_avalues(6,'A6');
insertintotest_up_bvalues(1,'BBBB1');
insertintotest_up_bvalues(2,'BBBB2');
insertintotest_up_bvalues(5,'BBBB5');
commit;
3、分别查看两个表中数据;
select'TBL_A',t.*fromTEST_UP_At
unionall
select'TBL_B',t.*fromTEST_UP_Bt
4、执行更新脚本,可以发现TEST_UP_A.VALUE值已变化;
updateTEST_UP_At
sett.value=
(selectb.value
fromTEST_UP_Bb
wheret.id=b.id
andrownum=1)