Update one table from another 1

[kumar7704]

http://tek-tips.com/viewthread.cfm?qid=932379

This doesn't work for me on Oracle. Any ideas why it is failing.

I get the following error:
ORA-00933: SQL command not properly ended

I do have the primary key declared on both tables.

Thanks for your help.

 

[carp]

Please post your actual code.

 

[Turkbear]

Hi,
Oracle and SqlServer use very different methods..

For Oracle, try

Update Table1 set (f1,f2,f3) = (select f1,f2,f3 from table2
where table1.key = table2.key);





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kumar7704]

Thanks, Turkbear.
It works.

 

[ddiamond]

If there are any records in Table1 that are not in Table2, the above statement will set f1, f2, and f3 to null on those records. If this is not the desired behavior, you could try the following:

Update Table1 set (f1,f2,f3) = (
select f1,f2,f3 from table2
where table1.key = table2.key)
Where table1.Key in (Select table2.key From table2);

 

[Turkbear]

Hi,
Great catch, ddiamond !
I have missed that too many times not to have included it in my code..







To Paraphrase:"The Help you get is proportional to the Help you give.."