CODEWITHSUNDEEP
mysqlsql
TopTomato
TopTomato

Reputation: 585

Why is my MySQL update statement, involving 2 tables not working?

my update statement which i was sure would work, is this:

UPDATE products SET manufacturers_id = 37 where products_id = 
(select products_id from     products_to_categories where categories_id = 35);

but in PHPMyAdmin, the error i'm getting is this:

#1242 - Subquery returns more than 1 row

but i want more than 1 row updated. i was trying to update 100 or more in a single statement. how can this be re-written so it will work? Do i have to use a join?

Upvotes: 1

Views: 52

Answers (2)

fthiella
fthiella

Reputation: 49089

You should use IN instead of =

UPDATE
  products
SET
  manufacturers_id = 37
WHERE
  products_id IN (SELECT products_id
                  FROM products_to_categories 
                  WHERE categories_id = 35);

or you could use a JOIN

UPDATE
  products INNER JOIN products_to_categories 
  ON products.product_d = products_to_categories.products_id
SET
  products.manufacturers_id = 37
WHERE
  products_to_categories.categories_id = 35

Upvotes: 3

echo_Me
echo_Me

Reputation: 37253

try this

 UPDATE products SET manufacturers_id = 37 where products_id in 
 (select products_id from     products_to_categories where categories_id = 35);

change = to IN

Upvotes: 2

Related Questions