CODEWITHSUNDEEP
mysql
Maverick
Maverick

Reputation: 25

MySQL: Update or Copy data from one table to another based on matching values

I am stumped on this one. I two tables:

Distributor

category         cat_match_id

Wallets
Shoes

Categories

id      cat_name

1       Wallets
2       Shoes

What I'm trying to do is search through the column category in each row in Distributor (thousands of rows), match that to the cat_name and take the associated id in Categories (112 rows), then take the id and place it in cat_match_id. I have search through every post I could find and have tried every solution, even combinations of solutions.

I have tried:

UPDATE Distributor
INNER JOIN Categories 
ON Distributor.category = Categories.cat_name
SET Distributor.category = Categories.id

And (tried with left, right, inner, outer, etc. joins)

UPDATE Distributor d
LEFT JOIN Categories c 
ON d.category = c.cat_name
SET d.cat_match_id = c.id
WHERE d.category = c.cat_name

Also using an example from MySQL:

UPDATE Distributor,Categories 
SET Distributor.cat_match_id = Categories.id
WHERE Distributor.category = Categories.cat_name

Those are just a few of the probably dozen or more snippets that I have tried. Is it because I'm trying to match 2 varchars and copy over a resulting int?

Upvotes: 0

Views: 900

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Your initial query is almost correct, barring some problems with your data, except that you are updating the wrong column. You should be doing this:

UPDATE Distributor d
INNER JOIN Categories c
    ON d.category = c.cat_name
SET d.cat_match_id = c.id;

Your current attempt is updating the Category.category field, which is already populated, and is what you were using in the join condition.

Note that I introduced aliases into your update query, which leaves it easier to read and more concise.

Upvotes: 1

Related Questions