How do I update table1 with data from table2

[alrightydooda]

Hi,

I have 2 tables and i need to update data in the first table with data from the second table. The following statement will not work but it will give you an idea of what i am trying to do.

UPDATE table1
SET table1.code_id = table2.system_id
WHERE table1.code_id = table2.code_id

I am using Oracle9i.

 

[jaggiebunnet]

try

UPDATE 
(SELECT table1.code_id t1_code, table2.system_id t2_sys
 FROM   table1, table2
 WHERE  table1.code_id = table2.code_id)
SET t1_code = t2_sys;

 

[alrightydooda]

Thanks this seems to be heading in the right direction. However now i'm getting error "ORA-01779 cannot modify a cloumn which maps to a non key-preserved table"

any idea what this means?

 

[jaggiebunnet]

Ah - forgot to mention, You need a primary
key/unique constraint on system_id in table2 to ensure that each row in table1 joins to AT MOST 1 row in table2.

try this instead

update table1
set code_id = (select system_id from table2 
               where table2.code_id = table1.code_id)
where exists (select system_id 
              from table2 
              where table2.code_id = table1.code_id)

 

[alrightydooda]

That did the trick thanks a million!