Mysql - How to use UPDATE with SELECT???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rahulephp
Forum Commoner
Posts: 28
Joined: Mon Oct 05, 2009 11:05 am

Mysql - How to use UPDATE with SELECT???

Post by 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
Image


related_category table
Image


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:

Code: Select all

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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Mysql - How to use UPDATE with SELECT???

Post by Christopher »

First of all, you don't want to duplicate the name. Just join the tables when you want the related information. Otherwise you could use REPLACE.
(#10850)
rahulephp
Forum Commoner
Posts: 28
Joined: Mon Oct 05, 2009 11:05 am

Re: Mysql - How to use UPDATE with SELECT???

Post by rahulephp »

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

Code: Select all

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
Post Reply