table1
number
111111
111111
123456
222222
333333
333333
444444
table2
numberone numbertwo
777777 111111
888888 222222
999999 333333
000000 444444
现在我想要的结果是更新table1 ,在table2中的numbertwo 这个字段存放的是table1的number列的某一部分数据,现在想执行更新,更新table1这个表如:table1的number=111111就变成了777777就是把table1的number字段等于table2中numbertwo的数据更新成对应的numberone数据
最后更新后table1的numer列数据如下
number
777777
777777
123456
888888
999999
999999
000000
number
111111
111111
123456
222222
333333
333333
444444
table2
numberone numbertwo
777777 111111
888888 222222
999999 333333
000000 444444
现在我想要的结果是更新table1 ,在table2中的numbertwo 这个字段存放的是table1的number列的某一部分数据,现在想执行更新,更新table1这个表如:table1的number=111111就变成了777777就是把table1的number字段等于table2中numbertwo的数据更新成对应的numberone数据
最后更新后table1的numer列数据如下
number
777777
777777
123456
888888
999999
999999
000000
using (select numberone,numbertwo from table2)tb2
on tb1.number = tb2.numbertwo when matched then
update set number = numberone
create table table1
(
numberone varchar2(30)
);create table table2
(
numberone varchar2(30),
numbertwo varchar2(30)
);insert into table1(numberone) values('111111');
insert into table1(numberone) values('111111');
insert into table1(numberone) values('123456');
insert into table1(numberone) values('222222');
insert into table1(numberone) values('222222');
insert into table1(numberone) values('333333');
insert into table1(numberone) values('333333');
insert into table1(numberone) values('444444');insert into table2(numberone,numbertwo) values('777777','111111');
insert into table2(numberone,numbertwo) values('888888','222222');
insert into table2(numberone,numbertwo) values('999999','333333');
insert into table2(numberone,numbertwo) values('000000','444444');select * from table1;
select * from table2;update table1 t1 set t1.numberone=decode((select t2.numberone from table2 t2 where t2.numbertwo=t1.numberone),null,t1.numberone,(select t2.numberone from table2 t2 where t2.numbertwo=t1.numberone))或者还有其它更优更好的方法,可以试试看。
number1 nvarchar2(255)
);create table table2 (
numberone nvarchar2(255),
numbertow nvarchar2(255)
);insert into table1(number1) values('111111');
insert into table1(number1) values('111111');
insert into table1(number1) values('123456');
insert into table1(number1) values('222222');
insert into table1(number1) values('333333');
insert into table1(number1) values('333333');
insert into table1(number1) values('444444');insert into table2(numberone, numbertow) values('777777', '111111');
insert into table2(numberone, numbertow) values('888888', '222222');
insert into table2(numberone, numbertow) values('999999', '333333');
insert into table2(numberone, numbertow) values('000000', '444444');select * from table1;
select * from table2;select * from table1, table2 where number1=numbertow;update table1 t1 set number1=(select t2.numberone from table2 t2 where t1.number1=t2.numbertow)
where exists (select 1 from table2 t2 where t1.number1=t2.numbertow);
特别是大数据量的情况下。。 update table1 t1 set t1.numberone=(select t2.numberone from table2 t2 where t1.numberone=t2.numbertwo)
where exists (select 1 from table2 t2 where t1.numberone=t2.numbertwo);
一楼使用merge的语法效率是最高的。
merge into 这种写法效率高些