SQL Update multiple rows and columns with subselect query

Question

I have the problem of needing to update multiple columns (2) in multiple rows (7) from a subselect query.

Here is what I have so far:

UPDATE commandtbl2 t1 
SET (attr, attr2) = (
SELECT attr, attr2 
FROM  commandtbl3 t2 
WHERE t1.cmd=t2.cmd
);

However when I let that run I get SQLCODE=-811, SQLSTATE=21000

(THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE)

Where is my mistake? It should change the 2 columns in 3 rows and leave the others rows as they are. I can only use SQL so no Java, PHP and so on.

Since this is DB2 solutions I have found online like:

UPDATE commandtbl2 t1
SET attr = t2.attr, attr2=t2.attr2
FROM commandtbl2 t1
JOIN commandtbl3 t2
ON t1.cmd = t2.cmd ;

Or

UPDATE
commandtbl2 t1
JOIN
commandtbl3 t2 ON t1.cmd=t2.cmd 
SET
t1.attr = t2.attr,
t1.attr2=t2.attr2;

don't work but throw exceptions.

Thanks for your help.

TheVagabond

Possible duplicate of [SQL update query using joins](http://stackoverflow.com/questions/982919/sql-update-query-using-joins) — HoneyBadger, Feb 14 '17 at 11:08
The error message is pretty clear - you update rows one at a time, so the subselect must return a single row given the conditions you provide. Find a way to uniquely identify rows in `commandtbl3` — mustaccio, Feb 14 '17 at 12:20
How many rows in commandtb2 and commandtbl3? It seems that you only have a few, so can you edit the rows into your question so we can see their values? — user2338816, Feb 15 '17 at 09:51

Gordon Linoff · Answer 1 · 2017-02-14T12:16:27.403

-1

I think your first version should work. But here is another idea:

UPDATE commandtbl2
    SET attr = t2.attr, attr2 = t2.attr2
    FROM commandtbl3 t2
    WHERE commandtbl2.cmd = t2.cmd ;

The version of DB2 matters. In V10, you may need two subqueries:

UPDATE commandtbl2
    SET attr = (SELECT t2.attr FROM commandtbl3 t2 WHERE commandtbl2.cmd = t2.cmd FETCH FIRST 1 ROW ONLY),
        attr2 = (SELECT t2.attr2 FROM commandtbl3 t2 WHERE commandtbl2.cmd = t2.cmd FETCH FIRST 1 ROW ONLY);

Note: Normally when using FETCH FIRST 1 ROW ONLY, you would have an ORDER BY.

edited Feb 14 '17 at 12:16

answered Feb 14 '17 at 11:42

Gordon Linoff

1,242,037
58
646
786

t2.attr is invalid in this context. SQLCODE=-206, SQLSTATE=42703 – Thevagabond Feb 14 '17 at 11:44
Are you sure that you are using DB2? – Gordon Linoff Feb 14 '17 at 11:45
DB2 v10.5 Windows. – Thevagabond Feb 14 '17 at 11:45
In your second version for v10 I get SQLCODE=-811, SQLSTATE=21000. The same error I get from my first query – Thevagabond Feb 14 '17 at 11:59
@Thevagabond . . . You need to choose which row you want. Try adding `FETCH FIRST ONE ROW ONLY` . . . That should work in your first version as well. – Gordon Linoff Feb 14 '17 at 12:16

score -2 · Answer 2 · answered Feb 14 '17 at 11:08

-2

Use a join:

UPDATE commandtbl2 t1 JOIN commandtbl3 t2 ON t1.cmd=t2.cmd SET t1.attr=t2.attr, t1.attr2=t2.attr2;

Problem is probably as stated, you try to update one row (or multiple) with the values of more rows.

answered Feb 14 '17 at 11:08

devsteff

108
1
7

I get SQLCODE=-104, SQLSTATE=42601 and it tells me that a FROM is missing before the JOIN – Thevagabond Feb 14 '17 at 11:23

SQL Update multiple rows and columns with subselect query

2 Answers2