I was trying to find a way to update some rows in a MySQL table where the condition for performing the update was based on a column in another table. Normally this isn’t an issue, just run
UPDATE table ... WHERE key_column IN (SELECT key_column FROM ...).
I was having some problems, however, because the table that needed to be updated uses a mutli-column primary key. Normally I would select the relevant rows using a
JOIN. Unfortunately, you can’t update the results of a
JOIN. How then to select the relevant rows?
Turns out it’s easier than I thought. You can still use the
WHERE ... IN clause, you just specify all of the columns that satisfy the condition:
UPDATE table ... WHERE (key_column1, key_column2) IN (SELECT key_column1, key_column2 FROM ...).
I did not see any information about this functionality in the MySQL Reference Manual (1, 2).