updating a MySQL table with ID numbers based on another table.

I have 2 tables, one called 'products' and one called 'sub_cats'

in the table products there is a column 'sub_cats' which instead of having ID numbers as one would expect, it has the full name of the sub category, found in the sub_cats table.

I need to get the ID number from the sub_cats table and put it in the right fields which corresponds to the right name in the sub_cats table as indicated in the code area below.



so its like...
products table:
ID,  sub_cat, title
1    knife   SK 1
2    fork    SF 1
3    fork    SF 2
4    spoon   SP 1
and in the sub_cats table its simply:
ID,  sub_cat
1    knife
2    fork
3    spoon
so I really need the products table to be more like:
ID,  sub_cat, title
1    1        SK 1
2    2        SF 1
3    2        SF 2
4    3        SP 1

Open in new window

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
update products , sub_cats 
  set products.sub_cat = sub_cats.id
where products.sub_cat = sub_cats.sub_cat

Open in new window

timbo007Author Commented:
Hah, just as easy as that, I never thought of using join type syntax but it makes perfect sense!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.