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?

score 21 · Answer 1 · answered Jun 30 '17 at 11:21

21

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)

answered Jun 30 '17 at 11:21

Thomas Mueller

48,905
14
116
132

This works, so the JOIN style syntax isn't supported? – lrkwz Mar 24 '19 at 14:11
1

@lrkwz best if you check in the documentation. – Thomas Mueller Mar 26 '19 at 07:23
@ThomasMueller Could you please respond to my question https://stackoverflow.com/questions/57200828/convert-mysql-update-query-to-a-query-which-support-on-h2-db. From http://www.h2database.com/html/commands.html#update, I reached a conclusion that Update does not work with JOIN. Am I right? – nantitv Jul 25 '19 at 14:34
Aren't subqueries a lot less efficient than joins? This seems like a bad compromise to make. – jsarma May 26 '22 at 15:30
@jsarma No, subqueries are not necessarily slower. – Thomas Mueller May 27 '22 at 20:45
The conventional wisdom is joins are faster than subqueries. It's possible some query optimizers have managed to narrow or eliminate the gap, but with the subquery you're essentially doing 2 sequential operations, whereas the join in parallelized. Having a subquery in an update also makes me nervous about locking behavior since between the select and update, there probably needs to be some kind of transactional guarantee that the data doesn't change. https://www.geeksforgeeks.org/sql-join-vs-subquery/ https://www.percona.com/blog/2017/09/25/avoid-shared-locks-from-subqueries-when-possible/ – jsarma May 31 '22 at 14:57
One or two blog posts that say "The advantage of a join includes that it executes faster." doesn't make joins execute faster, or change the "conventional wisdom". In German, there is a saying "Papier ist geduldig" which means "paper is patient", or "You can say what you like on paper". – Thomas Mueller Jun 02 '22 at 07:48
@jsarma "you're essentially doing 2 sequential operations" depends on the database engine, and is untrue for most database engines. "transactional guarantee that the data doesn't change": you have that guarantee within an SQL statement. – Thomas Mueller Jun 02 '22 at 07:51

Grigory Kislin · Answer 2 · 2022-09-21T20:07:55.457

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 inner SELECT you can use updated table columns
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

Sample SELECT WITH UPDATE:

UPDATE USER_DETAILS UD SET UD.GRADUATE_COMMENT=
(SELECT U.COMMENT FROM USERS U WHERE u.ID=UD.id) <-- ref to outer updated table
WHERE UD.GRADUATE_COMMENT IS NULL;

H2 update with join

2 Answers2

Linked