玉祥平台客服-15087858732
标题:
用一个表字段更新另一个表字段三种方法
[打印本页]
作者:
陈晓龙
时间:
2020-7-8 16:28
标题:
用一个表字段更新另一个表字段三种方法
--Oracle 用一个表字段更新另一个表字段三种方法
--1、建测试表如下
prompt PL/SQL Developer import file
prompt Created on 2016年7月29日 by wsq
set feedback off
set define off
prompt Disabling triggers for T_A...
alter table T_A disable all triggers;
prompt Disabling triggers for T_B...
alter table T_B disable all triggers;
prompt Truncating T_B...
truncate table T_B;
prompt Truncating T_A...
truncate table T_A;
prompt Loading T_A...
insert into T_A (id, name, demo)
values (1, 'aaa', 'bbb');
insert into T_A (id, name, demo)
values (2, 'ccc', 'ddd');
insert into T_A (id, name, demo)
values (3, 'eee', 'fff');
commit;
prompt 3 records loaded
prompt Loading T_B...
insert into T_B (id, name, demo)
values (1, null, null);
insert into T_B (id, name, demo)
values (2, null, null);
insert into T_B (id, name, demo)
values (3, null, null);
commit;
prompt 3 records loaded
prompt Enabling triggers for T_A...
alter table T_A enable all triggers;
prompt Enabling triggers for T_B...
alter table T_B enable all triggers;
set feedback on
set define on
prompt Done.
--2.方法一
UPDATE T_B B
SET B.NAME =
(SELECT NAME FROM T_A A WHERE A.ID= B.ID),
B.DEMO =
(SELECT DEMO FROM T_A A WHERE A.ID = B.ID);
--3.方法二
update T_B B
SET (name, demo) =
(SELECT name, demo FROM T_A A WHERE A.ID = B.ID)
--4.方法三
merge into T_B B
using T_A A
on (B.ID = A.ID)
when matched then
update set B.NAME = A.NAME, B.DEMO = A.DEMO
作者:
万望
时间:
2020-7-8 22:19
作者:
陈晓龙
时间:
2020-7-9 13:55
万望 发表于 2016-9-8 22:19
作者:
陈晓龙
时间:
2020-7-9 13:56
欢迎光临 玉祥平台客服-15087858732 (http://bbs.delit.cn/)
Powered by Discuz! X3.2
