sql - 使用公用键用来自另一个表的数据更新一个表

Question

使用公用键用来自另一个表的数据更新一个表

create table table1 (
  id varchar2(4),
  name varchar2(10),
  desc_ varchar2(10)
)


create table table2 (
  id varchar2(4),
  id_new varchar2(4)
)

insert into table1 values('1111', 'a', 'abc')
insert into table1 values('2222', 'b', 'def')
insert into table1 values('3333', 'c', 'ghi')
insert into table1 values('4444', 'd', 'jkl')

insert into table2 values('1111', '8080')
insert into table2 values('2222', '9090')

merge into table1 t1
using (select * from table2) t2
on (t1.id = t2.id)
when matched then update set t1.id = t2.id_new

error: ORA-27432: step does not exist for chain .

Answer 1

这应该有效:

update table1 t1
set id = coalesce((
  select id_new
  from table2 t2
  where t1.id = t2.id), id);

SQL Fiddle Demo

这是 merge 的替代方法:

merge into table1 t1
using (select * from table2) t2
on (1 = 1)
when matched then update set t1.id = t2.id_new where t1.id = t2.id

More Fiddle