tb1: ID chanpin money
01 手机 200
02 电冰箱 500
01 手机 300
tb2 id chanpin
01 手机A
02 电冰箱BSQLSERVER :UPDATE TB1 SET CHANPIN=TB2.CHANPIN FROM TB2 WHERE TB1.ID=TB2.ID
但是这个在PLSQL不行,求大神指导一下这个在ORACLE下怎么写?
01 手机 200
02 电冰箱 500
01 手机 300
tb2 id chanpin
01 手机A
02 电冰箱BSQLSERVER :UPDATE TB1 SET CHANPIN=TB2.CHANPIN FROM TB2 WHERE TB1.ID=TB2.ID
但是这个在PLSQL不行,求大神指导一下这个在ORACLE下怎么写?
--oracle無update from
update TB1 set CHANPIN=(select CHANPIN from TB2 WHERE TB1.ID=TB2.ID);
update TB1 set CHANPIN=(select CHANPIN from TB2 WHERE TB1.ID=TB2.ID and rownum<2);
01 手机A 200
02 电冰箱A 500
01 手机A 300
USING TB2 B
ON (A.ID = B.ID)
WHEN MATCHED THEN
UPDATE
SET A.chanpin = B.chanpin
--不會清空ID吧,只會清空tb2表中沒記錄的chanpin,忘加exists了
update TB1 set CHANPIN=(select CHANPIN from TB2 WHERE TB1.ID=TB2.ID and rownum<2)
where exists (select 1 from TB2 where TB1.ID=TB2.ID);
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;