How to update existing records using a conditional clause?

Question

I am new to Oracle SQL, so I have a question. I have two tables: table A and table B. Now table A and table B have the same column names, but in table A only one column (named "tracker") actually has data in it. The remaining columns in table A are empty ... What I need to do is update each record in table A so that the values ​​for the other columns are copied from table B, provided that the values ​​of the tracker columns from table A correspond to the tracker column in table B.

Any ideas?

Answer 1

MERGE INTO tableA a USING tableB b ON (a.tracker=b.tracker) WHEN MATCHED THEN UPDATE SET a.column1=b.column1, a.column2=b.column2; 

And if there are lines from B that don't exist in A:

 MERGE INTO tableA a USING tableB b ON (a.tracker=b.tracker) WHEN MATCHED THEN UPDATE SET a.column1=b.column1, a.column2=b.column2 WHEN NOT MATCHED THEN INSERT VALUES a.tracker,a.column1,a.column2; --all columns 

Answer 2

 create table a (somedata varchar2(50), tracker number , constraint pk_a primary key (tracker)); create table b (somedata varchar2(50), tracker number, constraint pk_b primary key (tracker)); / --insert some data insert into a (somedata, tracker) select 'data-a-' || level, level from dual connect by level < 10; insert into b (somedata, tracker) select 'data-b-' || -level, level from dual connect by level < 10; select * from a; SOMEDATA TRACKER -------------------------------------------------- ------- data-a-1 1 data-a-2 2 data-a-3 3 data-a-4 4 data-a-5 5 data-a-6 6 data-a-7 7 data-a-8 8 data-a-9 9 select * from b; SOMEDATA TRACKER -------------------------------------------------- ------- data-b--1 1 data-b--2 2 data-b--3 3 data-b--4 4 data-b--5 5 data-b--6 6 data-b--7 7 data-b--8 8 data-b--9 9 commit; update (select a.somedata a_somedata, b.somedata b_somedata from a inner join b on a.tracker = b.tracker) set a_somedata = b_somedata; select * from a; --see below for results-- --or you can do it this way: (issuing rollback to get data back in previous state) --for a one column update, either way will work, I would prefer the former in case there is a multi-column update necessary -- merge *as posted by another person* will also work update a set somedata = (select somedata from b where a.tracker = b.tracker ); select * from A; --see below for results-- -- clean up -- drop table a; -- drop table b; 

this will give you the results:

 SOMEDATA TRACKER -------------------------------------------------- ------- data-b--1 1 data-b--2 2 data-b--3 3 data-b--4 4 data-b--5 5 data-b--6 6 data-b--7 7 data-b--8 8 data-b--9 9 

here is the link to oracle documentation on UPDATE