Update columns in multiple tables with inner join

Edit Turns out there is an H2 database sitting on top of the mysql database. The query I write hits the H2 instead. I'll keep researching to see if this will work I have two tables I would like to update at the same time, and the values from one of them is determined by values stored in the other. My update query looks like this: ``` UPDATE table1 AS A INNER JOIN table2 AS B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2 SET A.COL3 = 'SOME VAL', B.COL4 = B.COL4 - A.COL4, B.COL5 = B.COL5 - A.COL4 WHERE A.ID IN (23,5,21) ``` I'm getting a syntax error that says 'Expected "SET"' right where I'm doing the INNER JOIN. I believe I should be able to do this join update per UPDATE multiple tables in MySQL using LEFT JOIN and http://dev.mysql.com/doc/refman/5.0/en/update.html. Does anybody know what my syntax error is? Update for posterity First, thanks to Thomas Mueller for his help. I ended up using the following syntax and as I found it somewhat confusing, I'm leaving it here for future viewers. ``` UPDATE TABLE1 SET(COL1, COL2) = ( SELECT T1.COL1 - T2.AMNT, T1.COL2 + T2.AMNT FROM TABLE1 T1 RIGHT JOIN TABLE2 T2 ON T1.COL3 = T2.COL3 AND T1.COL4 = T2.COL4 WHERE T2.ID = 23) WHERE EXISTS ( SELECT * FROM TABLE2 WHERE TABLE1.COL3 = TABLE2.COL3 AND TABLE1.COL4 = TABLE2.COL4 AND TABLE2.ID = 23) ``` Note: I had to use a join in the first select as I couldn't get the syntax we discussed below to work. As a result of using this method, if I get a list of table2 ids (23,5,21 in my original example) I have to do multiple update statements. If anybody knows a better way to do this, please let me know.