H2 update with join

Question

As development DB I am using MySQL, and for tests I am using H2 database. The following script works in MySQL very well, but it is fails on H2.

UPDATE `table_a`
JOIN `table_b` ON `table_a`.id=`table_b`.a_id
SET `table_a`.b_id=`table_b`.id

In the internet I found that h2 doesn't support UPDATE clause with JOIN. Maybe there is a way to rewrite this script without JOIN clause?

By the way, I am using liquibase. Maybe I can write UPDATE clause with it's xml language?

I tried the following script

UPDATE table_a, table_b
SET table_a.b_id = table_b.id
WHERE table_a.id = table_b.a_id

But I still getting errors. Seems, that H2 doesn't support updating multiple tables in one query. How can I rewrite this query in two different queries to collect ids and insert them?

Answer 1

Try something like this:

update table_a a
set a.b_id = (select b.id from table_b b where b.a_id = a.id)
where exists
(select * from table_b b where b.a_id = a.id)

Answer 2

I've spend a lot of time for this kind of UPDATE. Please find out my comment, maybe somebody find it usefull:

• For every rows in WHERE condition executed UPDATE for SET
• In case of error "Scalar subquery contains more than one row" - UPDATE for SET return more, than one row. Problem rows could be found with replace UPDATE by SELECT COUNT(*)

See also Scalar subquery contains more than one row