sql UPDATE SET WHERE condition gives error 1242

Question

I get the error 1242 Subquery returns more than 1 row

What I want is to update the column bio and set it to 'bio-plant' if the product contains those additions.

UPDATE product
SET bio = 'bio-plant'
WHERE ( SELECT distinct product.Id_Product
        FROM product , product_has_additions
        WHERE product_has_additions.code IN ('E170', 'E220', 'E296', 'E300', 'E306', 'E322', 'E330', 'E333', 'E334', 'E335', 'E336', 'E341', 'E392', 'E400', 'E401', 'E402', 'E406', 'E407', 'E410', 'E412', 'E414', 'E415', 'E422', 'E440', 'E464')
        AND product.Id_Product = product_has_additions.Id_Product
       );

Answer 1

I think that what you actually need is a join of the 2 tables:

UPDATE product p
INNER JOIN product_has_additions a
ON a.Id_Product = p.Id_Product
SET p.bio = 'bio-plant'
WHERE a.code IN (
  'E170', 'E220', 'E296', 'E300', 'E306', 'E322', 'E330', 'E333', 
  'E334', 'E335', 'E336', 'E341', 'E392', 'E400', 'E401', 'E402', 
  'E406', 'E407', 'E410', 'E412', 'E414', 'E415', 'E422', 'E440', 'E464'
)

Or, use EXISTS:

UPDATE product p
SET p.bio = 'bio-plant'
WHERE EXISTS (
  SELECT 1 FROM product_has_additions a
  WHERE a.Id_Product = p.Id_Product
    AND a.code IN (
     'E170', 'E220', 'E296', 'E300', 'E306', 'E322', 'E330', 'E333', 
     'E334', 'E335', 'E336', 'E341', 'E392', 'E400', 'E401', 'E402', 
     'E406', 'E407', 'E410', 'E412', 'E414', 'E415', 'E422', 'E440', 'E464'
  )
)