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).