Wednesday 13 June 2012

Update data in one table with data from another table

It's a tricky question asked in interviews:

create table a (col1 varchar2(10), col2 varchar2(10));
create table b (col1 varchar2(10), col2 varchar2(10));

insert into a VALUES ('1', 'FIELD2-1_A');
insert into B VALUES ('1', 'FIELD2-1_B');

insert into a VALUES ('2', 'FIELD2-2_A');
insert into B VALUES ('2', 'FIELD2-2_B');

SELECT * FROM A
SELECT * FROM B;

UPDATE a
   SET col2 = (SELECT col2
                 FROM b
                WHERE b.col1 = a.col1)
 WHERE EXISTS (SELECT 1
                 FROM b
                WHERE b.col1 = a.col1)


No comments:

Post a Comment