Mysql - How to use UPDATE with SELECT???

I have two tables:
1) products_category
-cat_id
-cat_name

2) related_category
-rel_cat_id (exaclty same as products_category.cat_id==related_category.rel_cat_id)
-rel_cat_name

products_category table
[IMG]http://www.ephpsolutions.com/scud/rrr/product_category.gif[/IMG]


related_category table
[IMG]http://www.ephpsolutions.com/scud/rrr/related_category.gif[/IMG]


I want to get cat_name from products_category and want to store in rel_cat_name in related_category 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

Recommended Answers

All 3 Replies

Try this one:

UPDATE related_category
SET related_category.rel_cat_name = 
( 
SELECT product_category.cat_name FROM product_category
WHERE related_category.rel_cat_id = product_category.cat_id LIMIT 1
)

Thank you buddy,
I got the solutions. Here it is:

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
commented: Great solution +0

This works @rahulphp ,Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.