CODEWITHSUNDEEP
mysqlsqljoinsql-update
casper
casper

Reputation: 461

mysql-update table from another table

How can i update a table if a value from another table. a illustrate is like this

table products

product_name | cat_name | id_cat
Item 1       |  sport   |   
Item 2       |  food    |
Item 3       |  fashion |

table category

id_cat | cat_name  
 1     | sport
 2     | food
 3     | fashion

please give me a query or function how to update field id_cat from table product which value from table category?

thanks

Upvotes: 1

Views: 154

Answers (1)

John Woo
John Woo

Reputation: 263693

you can simply join both tables,

UPDATE  products a
        INNER JOIN category b
            ON a.cat_name = b.cat_name
SET     a.id_cat = b.id_cat

for faster performance, add an index on column cat_name for both tables.

Upvotes: 1

Related Questions