5

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.

Community
  • 1
  • 1
Geoffrey Ochsner
  • 235
  • 3
  • 14
  • There is nothing wrong with your query on its own, check table or column names, maybe some are restricted words, try to backtick them, if its a part of a stored procedure or any other kind of code block check if previous statement is properly ended with semicolon and so on... – piotrm Apr 30 '13 at 22:28
  • 1
    Maybe you are using a very old version, like 4.1? If you don't know, run SELECT VERSION() – Federico Razzoli Apr 30 '13 at 22:42
  • 1
    Turns out I'm using mysql 5.5 BUT there's an H2 database that's sitting on top of it. So I'm getting the syntax error because I'm actually querying the H2 table. Will update question to reflect this but I'm less optimistic that this will work now. – Geoffrey Ochsner May 01 '13 at 13:46

1 Answers1

3

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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • Can I at a minimum do something like the following? I can't get formatting right. But I'm trying to update two column values on Table1 with a value from table2 where some condition on table2 UPDATE SUP_INV SI set (SI.Amnt_Reserved, SI.Amnt_Used) = (Select SI.Amnt_Reserved - JS.Amnt, SI.Amnt_Used + JS.Amnt FROM JOB_SUPPLY JS WHERE SI.SUPPLY_NAME = JS.SUPPLY_NAME AND SI.SUPPLY_MFR = JS.SUPPLY_MFR AND JS.ENTITY_ID IN (23,15,49) ) – Geoffrey Ochsner May 01 '13 at 16:44
  • I got the syntax for that from your post here https://groups.google.com/forum/?fromgroups=#!topic/h2-database/45DPbNVYxck – Geoffrey Ochsner May 01 '13 at 16:49
  • Yes that should work (well I didn't test, but it looks good). You may need to add a `where` condition, possibly `where exists(select ...)` to the `update` statement, unless all rows of this table need to be updated. Unfortunately, that could mean there is some repetition in the statement. – Thomas Mueller May 01 '13 at 17:46