Update multiple rows using select statements

Question

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?

Correlate both your Select Statements with Table2 using joins and then fire the update on Table2 — SoulTrain
– SoulTrain, Commented Jan 28, 2015 at 21:53

David Faber · Accepted Answer · 2015-01-28 22:34:55Z

2

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).

answered Jan 28, 2015 at 22:34

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update multiple rows using select statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related