Update table with a subquery which is returning more than one row

Question

SELECT (b.descr || ' - ' || c.descr) description
FROM table1 a
    INNER JOIN table2 b ON a.account = b.account
    INNER JOIN table3 c ON a.product = c.product
WHERE a.descr = ' ' ;

How to update a table using the above subquery? its returning more than one row nearly 8000 rows? If you have any solutions for this please share with me?

Answer 1

I don't understand what you exactly want to do, but you can use the subquery in a subselect statement :

UPDATE table1 a SET a.descr = (
    SELECT MAX(b.descr || ' - ' || c.descr)
    FROM table2 b, table3 c
    WHERE b.account = a.account AND c.product = a.product
)
WHERE a.descr = ' '

The MAX() will just choose a value for you. If you want to choose it yourself, either restrict the subquery further

Answer 2

In both Oracle & SQL Sever, if sub query return more than 1 row, database will report error.

In your case, if sub query resulted values is same, simply use MAX() or MIN() function to let DB select a value.

Answer 3

Try:

UPDATE a SET descr = (b.descr || ' - ' || c.descr)
FROM table1 a
    INNER JOIN table2 b ON a.account = b.account
    INNER JOIN table3 c ON a.product = c.product
WHERE a.descr = ' ' ;

Where there are multiple rows, table1 will end up with the last one seen.

Rob