CODEWITHSUNDEEP
oracle-databaseplsqlsql-updatecorrelated-subquery
Heath Hackett
Heath Hackett

Reputation: 47

How to update a table based on the values in another table?

I am trying to update a column in one table with the values contained in the column from another table. I've tried the following but it gives me an error saying it returns more than one row.

update Table1 set description1 = (select description2 from Table2)
where customer_id in (select customer_id from Table2);

Any guidance?

Upvotes: 0

Views: 256

Answers (2)

mahi_0707
mahi_0707

Reputation: 1062

It seems select description2 from Table2 is returning more than one row. Since Oracle does not know to which value(description) to update exactly.

Use a WHERE CLAUSE and Filter the rows to 1 if you want to update all values to same value(description).Check and run if the query select description2 from Table2 returns only 1 row.

Else use keys for correlated update as suggested by Justin Cave.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

To do a correlated update, your subquery has to return a single row. Almost always, you do this by correlating the key that tells you which row from table2 maps to which row from table1. Assuming that both tables have a column named key that is unique

UPDATE table1 t1
   SET description = (SELECT t2.description2
                        FROM table2 t2
                       WHERE t1.key = t2.key)
 WHERE t1.customer_id IN (SELECT t2.customer_id
                            FROM table2)

Upvotes: 1

Related Questions