Refresh a table with a subquery that returns 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? his return of more than one row of nearly 8,000 lines? If you have any solutions for this, please share with me?

Answer 1

I don’t understand what you want to do exactly, but you can use the subquery in the subquery instructions:

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 = ' '

MAX () will just pick a value for you. If you want to select it yourself, limit the additional subquery

Answer 2

In Oracle and SQL Sever, if a sub query returns more than 1 row, the database will report an error.

, sub- , MAX() MIN(), DB .

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 several rows, table 1 will be shown last.

Rob