Mysql - How to use UPDATE with SELECT???

[rahulephp]

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

 

 

related_category table

 

 

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

[Mchl]

Not at all.

 

Use join in UPDATE's table reference clause

 

UPDATE related_category AS rc 
INNER JOIN product_category AS pc 
ON rc.rel_cat_id = pc.cat_id
SET rc.rel_cat_name = pc.cat_name

[rahulephp]

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

[Mchl]

That's pretty much the same, except I used aliases to make it less verbose.

[rahulephp]

Yeah.. i am thankful to you for this