Oracle: UPDATE SQL statement

Updating a table with data from another table

Source and destination tables, inserting test data.

create table tq84_dest ( id number not null, val varchar2(5));
create table tq84_src  ( id number not null, val varchar2(5));
 
begin
 insert into tq84_dest  values(1, 'i'  );
 insert into tq84_dest  values(3, 'iii');
 insert into tq84_dest  values(4, 'iv' );
 
 insert into tq84_src values (1, 'one');
 insert into tq84_src values (2, 'two');
 insert into tq84_src values (4, 'four');
 
 commit;
end;
/

In the following update statement, the exists clause is technically not necessary because tq84_src.id is declared as not null.

However, if tq84_src.id were nullable, the update statement would overwrite tq84_dest with null where no matching record is found in tq84_src. Thus, IMHO, it's always good practice to add such a safeguard.

update  tq84_dest d
   set d.val = (select s.val from tq84_src s where s.id = d.id)
where
   exists      (select null  from tq84_src s where s.id = d.id);

In the following snippet, a primary key is added to prevent the ORA-01779: cannot modify a column which maps to a non key-preserved table error.

alter table tq84_src add primary key (id);

update (
   select
      s.val s_val,
      d.val d_val
   from
      tq84_dest d  join
      tq84_src  s on s.id = d.id
)
set
  d_val = s_val;

Alternatively, using the merge statement:

merge into tq84_dest d
using ( select id, val from tq84_src ) s
on    ( d.id = s.id )
when matched then update set d.val = s.val;

Cleaning up

drop table tq84_dest;
drop table tq84_src;

Oracle: UPDATE SQL statement

Updating a table with data from another table

See also