update sql getting select more than one row

Question

Following query is throwing the error [SQL0811]

It says that the result of the SELECT returns more than one row. But when I run the queries individually, they return one row per key.

Think I need to have another WHERE clause checking table 2 and table 1. But how do I get visibility to table 2 outside of the SELECT and into the WHERE clause?

I tried something like this before the EXISTS:

where a.key1 = b.key1 and a.key2 = b.key2 and a.key3  b.key3, 

But when I run that I get the error:

column or global variable 'key1' not defined

UPDATE table 1 AS a
SET (a.col1, a.col2, a.col3) = (
    SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
    FROM table 2 b
    LEFT OUTER JOIN table 1 c
    ON b.key1 = c.key1
      AND b.key2 = c.key2
      AND b.key3 = c.key3
)
WHERE (a.col1 = ' ' OR a.col2 = ' ' OR a.col3 = ' ')
  AND EXISTS(
        SELECT 1
        FROM table c trk
                 INNER JOIN table d sts ON trk.key1 = sts.key1
        WHERE trk.key1 = a.key1
          AND sts.status IN (' ', 'REQ')
    )

Answer 1

Gordon has the answer for why you are getting the SQL0811, and how to fix it. Let's break down the update statement:

UPDATE table 1 AS a
SET (a.col1, a.col2, a.col3) = (
    SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
    FROM table 2 b
    LEFT OUTER JOIN table 1 c
    ON b.key1 = c.key1
      AND b.key2 = c.key2
      AND b.key3 = c.key3
)
WHERE ...

Here it looks like you are trying to update table 1 from a sub-query. But, that subquery has no connection to a. So it is returning all the rows in table 2 plus the matching rows from table 1 with nulls where there is no match. Since table 2 has multiple rows in it, you are getting SQL0811. You need to use a correlated sub-query as Gordon suggested to avoid returning all the rows in table 2 for each row to be updated.

UPDATE table 1 AS a
SET (a.col1, a.col2, a.col3) = (
    SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
    FROM table 2 b
    WHERE b.key1 = a.key1
      AND b.key2 = a.key2
      AND b.key3 = a.key3
)
WHERE ...

But, it also appears that there is not a row in table 2 for each row in table 1 which is why you get the SQL0407. In order to prevent that, you need to only update rows in table 1 that have a row in table 2. This is handled by adding the following to the outer WHERE clause:

UPDATE table 1 a
   ...
WHERE (key1, key2, key3) in (SELECT key1, key2, key3 FROM table 2)

Now, it occurs to me, though you didn't specify this outright, that you might want to set (col1, col2, col3) to ('N', 'N', 'N') where there is not a table 2 row that matches the table 1 row. As the above example only updates matching rows, it will not get the unmatching rows. You can do this with a separate UPDATE, or, to include that possibility in the same UPDATE as the first, you will need a combination of Gordon's answer with a modification of your query. You still need the correlated sub-query, and you still need a left join, but you need all the rows from table 1 plus the matching rows from table 2, not the other way around. That looks like this:

UPDATE table 1 AS a
SET (a.col1, a.col2, a.col3) = (
    SELECT coalesce(c.col1, 'N'), coalesce(c.col2, 'N'), coalesce(c.col3, 'N')
    FROM table 1 b
    LEFT OUTER JOIN table 2 c
      ON b.key1 = c.key1
        AND b.key2 = c.key2
        AND b.key3 = c.key3
    WHERE a.key1 = b.key1
      and a.key2 = b.key2
      and a.key3 = b.key3
)
WHERE ...

And for completeness, you wrote:

I tried something like this before the EXISTS:

where a.key1 = b.key1 and a.key2 = b.key2 and a.key3 b.key3,

But when I run that I get the error:

column or global variable 'key1' not defined

That is because b is in scope only for the sub-select, and cannot be found in the outer where clause. So b.key1 is not defined there. As you can see, SQL error messages are not always the best.

Answer 2

I'm pretty sure you want a correlated subquery for the set query:

UPDATE table1 a
    SET (a.col1, a.col2, a.col3) = (
        SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
        FROM table2 b
        WHERE b.key1 = a.key1 AND
              b.key2 = a.key2 AND
              b.key3 = a.key3
       )
    WHERE . . .;

EDIT:

If you want to ensure that the subquery in the SET returns exactly one row and the columns are never NULL, then use aggregation:

UPDATE table1 a
    SET (a.col1, a.col2, a.col3) = (
        SELECT coalesce(MAX(b.col1), 'N'), coalesce(MAX(b.col2), 'N'), coalesce(MAX(b.col3), 'N')
        FROM table2 b
        WHERE b.key1 = a.key1 AND
              b.key2 = a.key2 AND
              b.key3 = a.key3
       )
    WHERE . . .;