CODEWITHSUNDEEP
sqloracle-database
O P
O P

Reputation: 2365

Update multiple rows using select statements

Let's say I have these tables and values:

Table1
------------------------
ID | Value
------------------------
 2 | asdf
 4 | fdsa
 5 | aaaa

Table2
------------------------
ID | Value
------------------------
 2 | bbbb
 4 | bbbb
 5 | bbbb

I want to update all the values in Table2 using the values in Table1 with their respective ID's.

I know I can run this:

UPDATE Table2
SET Value = t1.Value
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

But what can I do if Table1 and Table2 are actually select statements with criteria? How can I modify the SQL statement to take that into consideration?

Upvotes: 0

Views: 10504

Answers (1)

David Faber
David Faber

Reputation: 12486

This is how such update queries are generally done in Oracle. Oracle doesn't have an UPDATE FROM option:

UPDATE table2 t2
   SET t2.value = ( SELECT t1.value FROM table1 t1
                     WHERE t1.ID = t2.ID )
 WHERE EXISTS ( SELECT 1 FROM table1 t1
                 WHERE t1.ID = t2.ID );

The WHERE EXISTS clause will make sure that only the rows with a corresponding row in table1 are updated (otherwise every row in table2 will be updated; those without corresponding rows in table1 will be updated to NULL).

Upvotes: 2

Related Questions