MySql Join Query Problem

Question

I am trying to update all transaction_subcategory_id's in transaction to match_subcategory_id in match where match_name in match is the same as transaction_subcategory_name in transaction.

Its got to be simple, just not getting very far with it...

this is our latest attempt...

UPDATE transaction JOIN match ON match.match_name = transaction.transaction_name
SET transaction.transaction_subcategory_id = match.match_subcategory_id;

tables

match
--------------------------
match_id
match_name
match_subcategory_id

transaction
--------------------------
transaction_id
transaction_name
transaction_subcategory_id

Answer 1

UPDATE `transaction` SET `transaction`.`transaction_subcategory_id` = `match`.`match_subcategory_id`
JOIN `match` ON `match`.`match_name` = `transaction`.transaction_name;

Just switch the position of the SET and the JOIN and it should work.

MySQL Docs on UPDATE statement

The docs only show the implicit join:

UPDATE `transaction`, `match` SET `transaction`.`transaction_subcategory_id` = `match`.`match_subcategory_id`
WHERE `match`.`match_name` = `transaction`.`transaction_name`;

But they say you can use any JOIN syntax that works on SELECT on UPDATE, so your query should work when switching SET and JOIN.

Answer 2

try with SET before JOIN on your query