This content has been marked as final. Show 4 replies
debUVI wrote:The SQL/PLSQL forum would be a better place for this quesiton but since it is here ...
Hello everyone. I am am trying to do an update statement with an inner join. I have found several examples of SQL statements that work with Sql server and mysql but they don't work in Oracle. Does anyone know the proper way in Oracle 10G? I am trying to update all fields in one table from fields in another table.
TL3.name = TL2.name,
TL3.status = TL2.status,
TL3.date = TL2.date
FROM table3 TL3 JOIN table2 TL2
ON (TL3.unique_id = TL2.unique_id);
any help will be appreciated.
Use a correlated subquery in the SET clause, something like (untested)
Edited by: riedelme on Feb 10, 2011 7:20 AM
UPDATE table3 tl3 SET (TL3.name ,TL3.status = TL2.status,TL2.date) = (select tl2.name, tl2status, tl2.date FROM table2 TL2 join table3 ON (TL3.unique_id = TL2.unique_id) where exists ( select 0 from table2 where table2.unique_id = tl3.unique_id )
You can also use MERGE, like this:
Unlike UPDATE, this lets you avoid essentially doing the same sub-query twice: once in the SET clause and then again in the WHERE clause.
MERGE INTO table3 dst USING ( SELECT unique_id , name , status , dt -- DATE is not a good column name FROM table2 ) src ON (dst.unique_id = src.unique_id) WHEN MATCHED THEN UPDATE SET dst.name = src.name , dst.status = src.status , dst.dt = src.dt ;
Like UPDATE, you don't acutally join the table being changed (table3 in this case) to the other table(s); that is, the FROM clause of the suib-query does not include table3.
Riedelme is right; you'll get better response to SQL questions like this in the SQL and PL/SQL forum:
PL/SQL and SQL
Thank you both very much. Both options worked. Sorry about the placement of the post. Thanks again.
This will also update the required rows.
Set (Name, Status, Date) =
Select Name, Status, Date
Where Table2.Unique_ID = Table3.Unique_ID
Where Unique_ID In (Select Unique_ID From Table2)