CODEWITHSUNDEEP
mysqldatabase
ajsmart
ajsmart

Reputation: 193

Adding/Editing MYSQL keys

I am working on a table within a MySQL database that has the following columns:

 - ItemID (auto incremented key)
 - Item
 - CategoryID (empty column)
 - Category
 - SubcategoryID (empty column)
 - Subcategory

In other tables within the same database I have a list of categories and subcategories with their unique IDs, and I would like to take those IDs from the existing tables and port them over to the item table I described above. After porting the IDs over, I intend to delete the category and subcategory entries, as the ID will serve as enough reference for my purposes.

I could do this manually, and I will do it manually if necessary, but going through several thousand entries in MySQL doesn't sound like my ideal afternoon.

My question is simply, is there a way to use my existing tables to alter my item table (above) in the way I described?

Thanks!

Upvotes: 0

Views: 39

Answers (1)

Dan
Dan

Reputation: 11104

So you have a category table with an id and category field and the category field matches items.category table.

To update the items.category_id, JOIN to the category table on items.category <=> category.category and UPDATE items.category_id with value in category.id

UPDATE items 
  JOIN category ON items.category <=> category.category
SET items.category_id = category.id;

You may have to change some columns names, but hopefully they are obvious.

Repeat this for subcategory and any other fields.

Note: <=> is NULL-safe equal, so rows with NULL values will get updated correctly

Upvotes: 1

Related Questions