How to use UPDATE with SELECT???

[rahulephp]

Mysql - How to use UPDATE with SELECT???

I have two tables:
1) products_catego ry
-cat_id
-cat_name

2) related_categor y
-rel_cat_id (exaclty same as products_catego ry.cat_id==rela ted_category.re l_cat_id)
-rel_cat_name

products_catego ry table



related_categor y table



I want to get cat_name from products_catego ry and want to store in rel_cat_name in related_categor y tabel. and the query should be only one.

Not sure how will it works.
I thought it would be something like:

UPDATE related_category 
SET related_category.rel_cat_name = 
( 
SELECT product_category.cat_name FROM product_category
INNER JOIN related_category
ON related_category.rel_cat_id = product_category.cat_id
)

But it doesn't works,
Please assist. Thanks in advance

[patjones]

The problem is that your sub-query is returning a set of records, whereas your main query refers to one record at a time. You could correlate the two queries, but that would be inefficient.

Just try updating the JOINed recordset altogether:

UPDATE related_category INNER JOIN product_category
       ON related_category.rel_cat_id = product_category.cat_id
SET related_category.rel_cat_name = product_category.cat_name;

Pat

[rahulephp]

Did, works excellent, thanks a lot