Subquery returns more than 1 row while update

Question

I am writing one simple SQL query to update my table .

here is my code

$qry = mysql_query("update product set cat_tree =(select cat_name from cat_details where product.category = cat_details.cat_id )");

When ever i tried to run this code its giving error message

#1242 - Sub query returns more than 1 row

What is the problem ? I tried with so many solutions like "IN" "ANY" . But all examples are for insert query only.

Thanks in advance

That query returns multiple rows, you need to make it return 1 row. So there is only 1 value for it to set. — chris85, Mar 29 '16 at 12:24
It will make the query return only 1 row. It could be the incorrect result though. You will need to give it an appropriate `order`. — chris85, Mar 29 '16 at 12:34

score 0 · Answer 1 · answered Mar 29 '16 at 12:25

0

Try this

update product p join cat_details c on p.category = c.cat_id
set p.cat_tree = c.cat_name
where p.category =  c.cat_id

answered Mar 29 '16 at 12:25

Nawaz

score 0 · Accepted Answer · answered Mar 29 '16 at 12:47

0

You should try LIMIT like this...

$qry = mysql_query("update product set cat_tree =(select cat_name from cat_details where product.category = cat_details.cat_id LIMIT 1)");

answered Mar 29 '16 at 12:47

Satish Jaiswal

score 0 · Answer 3 · edited May 23 '17 at 12:23

0

What you are looking for is an UPDATE query with a JOIN (Ref):

UPDATE product AS p 
  JOIN cat_details AS c
  ON p.category = c.cat_id 
SET p.cat_tree = c.cat_name;

edited May 23 '17 at 12:23

Community

answered Mar 29 '16 at 13:03

Reversal

3 Answers3