CODEWITHSUNDEEP
mysql
Manish Pradhan
Manish Pradhan

Reputation: 1188

How can I update one table in reference to another table in MYSQL?

Here is how I have my tables set up - 

Products
_______

P_Id  Desc  
1      Apple  
2     Orange  
3     Banana


Categories
__________

P_Id  Cat_Id  
1      200  
2      200  
3      205

Now I need to update the field 'Desc' field from table "Products" where 'Cat_Id' field from table "Categories" is equal to 200. Any help would be very appreciated.

Upvotes: 1

Views: 1996

Answers (2)

Mark Byers
Mark Byers

Reputation: 838896

Here's one way to do it:

UPDATE Products
SET `desc` = 'foo'
WHERE P_id IN (SELECT P_Id FROM Categories WHERE Cat_Id = 200)

Another approach is to use a multiple-table update

UPDATE Products, Categories
SET Products.`desc` = 'foo'
WHERE Products.P_id = Categories.P_ID
AND Categories.Cat_Id = 200

By the way, desc is a very poor choice for the name of a column. It is a reserved word. All the time saved by not typing description in full will be quickly lost when you have to debug queries that don't work when you forget the backticks.

Upvotes: 3

heretolearn
heretolearn

Reputation: 6555

Try this:

UPDATE products
set `Desc` = 'xxx'
WHERE P_id IN (SELECT P_Id FROM Categories WHERE Cat_Id = 200);

Upvotes: 0

Related Questions