求教一条更新语句笔试题
两个表A和B,字段相同,内容不同,不过ID关联
A: id name age address postcode
B: id name age address postcode
将B中age大于40岁的address和postcode字段更新到A表
请高手赐教Oracle和MySQL两个版本的
我的想法是:select into 和 oracle 的 decode函数
两个表A和B,字段相同,内容不同,不过ID关联
A: id name age address postcode
B: id name age address postcode
将B中age大于40岁的address和postcode字段更新到A表
请高手赐教Oracle和MySQL两个版本的
我的想法是:select into 和 oracle 的 decode函数
drop table b;create table a(id varchar(10), name varchar(20), age int, address varchar(40), postcode varchar(8));
insert into a
select
'0001', 'luoyoumou1', 30, 'HuNanHengdongXian', '421431' union all select
'0002', 'luoyoumou2', 31, 'BeijingChangpingHuilongguan', '101206' union all select
'0003', 'luoyoumou3', 41, 'TianjingWumingjie', '333333';
create table b(id varchar(10), name varchar(20), age int, address varchar(40), postcode varchar(8));
insert into b
select
'0001', 'luoyoumou1', 30, '湖南衡东县', '421433' union all select
'0002', 'luoyoumou2', 31, '北京市昌平区回龙观', '101207' union all select
'0003', 'luoyoumou3', 41, '天津无名街', '333388';select * from a;
select * from b;update a
set a.address=b.address,
a.postcode=b.postcode
from a innser join b on a.id=b.id and b.age>40;update a
set a.address=b.address,
a.postcode=b.postcode
from a, b where a.id=b.id and b.age>40;
select * from a;
drop table a;
drop table b;create table a(id varchar2(10), name varchar2(20), age number(3,0), address varchar2(40), postcode varchar2(8));insert into a(id, name, age, address, postcode) values('0001', 'luoyoumou1', 30, 'HuNanHengdongXian', '421431');
insert into a(id, name, age, address, postcode) values('0002', 'luoyoumou2', 31, 'BeijingChangpingHuilongguan', '101206');
insert into a(id, name, age, address, postcode) values('0003', 'luoyoumou3', 41, 'TianjingWumingjie', '333333');create table b(id varchar2(10), name varchar2(20), age number(3,0), address varchar2(40), postcode varchar2(8));insert into b(id, name, age, address, postcode) values('0001', 'luoyoumou1', 30, '湖南衡东县', '421433');
insert into b(id, name, age, address, postcode) values('0002', 'luoyoumou2', 31, '北京市昌平区回龙观', '101207');
insert into b(id, name, age, address, postcode) values('0003', 'luoyoumou3', 41, '天津无名街', '333388');update a set (a.address,a.postcode) = (select b.address,b.postcode from b where b.id = a.id and b.age > 40)
where exists (select 1 from b where b.id = a.id and b.age > 40);
1.update A set A.address= (select B.address from B where A.id=B.id and B.age>40),
A.postcode=(select B.postcode from B where A.id=B.id and b.age>40)
where exists (select 1 from B where A.id=B.id and B.age>40);
2.采用merge的方法:
merge into A
using (select id name age address postcode from B where age>40) S
on S.id=A.id
when matched then
update set address=S.address,postcode=S.postcode;
应该有两种方法可以实现的:
1.update A set A.address= (select B.address from B where A.id=B.id ),
A.postcode=(select B.postcode from B where A.id=B.id )
where exists (select 1 from B where A.id=B.id and B.age>40);
2.采用merge的方法:
merge into A
using (select id ,name, age, address, postcode, from B where age>40) S
on (A.id=S.id)
when matched then
update set address=S.address,postcode=S.postcode;