Multiple UPDATE from SELECT with multiple rows in WHERE clause [Mysql]

Question

I have this query:

UPDATE Table1 SET Table1.parameterA = 1
  WHERE Table1.parameterB = ? 
  AND Table1.parameterC = (
    SELECT Table2.id 
    FROM Table2 
    WHERE Table2.parameterD = ? AND Table2.date > ?
  )

This is not working because the select is returning multiple rows:

{ Error: ER_SUBQUERY_NO_1_ROW: Subquery returns more than 1 row }

Is it possible a workaround to do this multiple update?

try changing AND Table1.parameterC = ( to AND Table1.parameterC IN ( — Jameson the dog
– Jameson the dog, Commented Jul 13, 2017 at 9:06

Jameson the dog · Accepted Answer · 2017-07-13 09:08:39Z

3

when using = SQL expects a single value.

if you want to work with a list of results - you should use IN like this:

UPDATE Table1 SET Table1.parameterA = 1
   WHERE Table1.parameterB = ? 
   AND Table1.parameterC IN (
     SELECT Table2.id 
       FROM Table2 
       WHERE Table2.parameterD = ? AND Table2.date > ?
   )

answered Jul 13, 2017 at 9:08

Jameson the dog

1,8061 gold badge12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple UPDATE from SELECT with multiple rows in WHERE clause [Mysql]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related