Update columns in multiple tables with inner join

Question

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.

Answer 1

H2 does not support updating two tables at the same time within one SQL statement. You would need to use two statements. For the supported syntax, see the UPDATE statement railroad diagram.