CODEWITHSUNDEEP
sql
Prem
Prem

Reputation: 37

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

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?

Upvotes: 0

Views: 1227

Answers (3)

Rob Farley
Rob Farley

Reputation: 15849

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

Upvotes: 0

Rock
Rock

Reputation: 263

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.

Upvotes: 1

Steve Schnepp
Steve Schnepp

Reputation: 4680

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

Upvotes: 1

Related Questions