CODEWITHSUNDEEP
mysqlsql
Tzadik Even
Tzadik Even

Reputation: 121

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

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?

Upvotes: 0

Views: 209

Answers (2)

forpas
forpas

Reputation: 164139

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

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.

Upvotes: 0

Related Questions