tb1: ID chanpin money
01 手机 200
02 电冰箱 500
01 手机 300
tb2 id chanpin
01 手机A
02 电冰箱B
SQLSERVER :UPDATE TB1 SET CHANPIN=TB2.CHANPIN FROM TB2 WHERE TB1.ID=TB2.ID
但是这个在PLSQL不行,求大神指导一下这个在ORACLE下怎么写?
------解决方案--------------------
create table tb1(id int,chanpin varchar(20),money number);
create table tb2(id int,chanpin varchar(20));
insert into tb1 values(01,'手机',200);
insert into tb1 values(02,'电冰箱',500);
insert into tb1 values(03,'手机',300);
insert into tb2 values(01,'手机A');
insert into tb2 values(02,'电冰箱B');
select * from tb1;
select * from tb2;
--第一种方法
update tb1 a1 set chanpin=(select chanpin from tb2 a2 where a1.id=a2.id)
where exists (select 'x' from tb2 a2 where a1.id=a2.id);
rollback;
--第二种
merge into tb1 a1 using tb2 a2 on (a1.id=a2.id)
when matched then update set a1.chanpin=a2.chanpin;
rollback;