SQL query returns "more than one row returned by a subquery used as an expression"

Question

I have two tables:

table1 has columns name a,b and c.
table2 has columns name d and e.

I need to set table1.a with the value of table1.b only if table1.c=table2.d and table2.e='true' (it's a bool).

I wrote the following:

UPDATE table1 SET a=(

SELECT t1.b
FROM table1 t1

INNER JOIN table2 t2

ON t1.c = t2.d

WHERE t2.e = 'true');

and got of course:

ERROR: more than one row returned by a subquery used as an expression

********** Error **********

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

How do I change this to work?

The error is pretty clear, so you'll have to figure out which of those multiple values is the one you want to use. We can't help you with that choice. — HoneyBadger, May 07 '20 at 09:06
Does this answer your question? [MySQL/SQL: Update with correlated subquery from the updated table itself](https://stackoverflow.com/questions/839938/mysql-sql-update-with-correlated-subquery-from-the-updated-table-itself) — Serg, May 07 '20 at 09:13

forpas · Answer 1 · 2020-05-07T13:14:24.377

1

Join the tables like this:

UPDATE table1 t1
INNER JOIN table2 t2
ON t1.c = t2.d
SET t1.a = t1.b
WHERE t2.e;

If (as you say) t2.e is boolean then WHERE t2.e is enough.
If it was varchar then you would need WHERE t2.e = 'true'.

edited May 07 '20 at 13:14

answered May 07 '20 at 09:26

forpas

160,666
10
38
76

score 0 · Answer 2 · answered May 07 '20 at 11:47

I would recommend exists:

UPDATE table1 t1 
    SET t1.a = t1.b
    WHERE EXISTS (SELECT 1
                  FROM table2 t2
                  WHERE t1.c = t2.d AND t2.e = 'true'
                 );

For performance you want an index on table2(d, e). Using exists means that MySQL will not attempt to update a row twice if there are multiple matching rows in table2.

SQL query returns "more than one row returned by a subquery used as an expression"

2 Answers2