Clever Geek Handbook

H2 update with attachment

I am using MySQL as development database and I am using H2 database for tests. The following script works pretty well in MySQL, but it doesn't work in H2.

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

I found on the internet that h2 doesn't support the UPDATE

c JOIN

. Maybe there is a way to rewrite this script without a suggestion JOIN

?

By the way, I use the educational base. Maybe I can write a sentence UPDATE

with this 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 am still getting errors. H2 doesn't seem to support updating multiple tables in a single query. How can I rewrite this query in two different queries to collect ids and insert them?

+3


source to share


1 answer


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)
+2


source






More articles:


All Articles