This is a simple one SQL I used yesterday for the Oracle application development. I have two tables which have two common fields (let us say, table_id and col1). The col1 in table1 is empty since it is a newly created table, and the col1 in table2 has the data but I will not use table2 since it is a old table. What I need to do is to move all col1 data from table2 to table1. The challenge is Table1 and Table2 do not have the exact same data on table_id.
First I used the following SQL statement to update the col1 in the table1 from column value in table2:
update table1
set col1=(select col1 from table2 where table2.table_id=table1.table_id)
But this statement update all rows in my first table even these rows whoes table_id was not found in the table2 with value null.
Someone suggested I should try UPDATE … FROM syntax in the statement, but I found out this syntax is not available in Oracle.
So I had to change the SQL statement to the following, and it worked.
update table1
set col1= (select col1 from table2
where table2.table_id=table1.table_id)
where Exists(select * from table2 where table2.table_id=table1.table_id)
Now this statement will only update those rows having a corresponding one in table2.
Its working fine……..Thx
[Reply]