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?

Compo

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)

Koby

This works, so the JOIN style syntax isn't supported? – Lustrous

@Lustrous best if you check in the documentation. – Koby

@ThomasMueller Could you please respond to my question #57201328. From h2database.com/html/commands.html#update, I reached a conclusion that Update does not work with JOIN. Am I right? – Caption

Aren't subqueries a lot less efficient than joins? This seems like a bad compromise to make. – Lefthand

@Lefthand No, subqueries are not necessarily slower. – Koby

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. geeksforgeeks.org/sql-join-vs-subquery percona.com/blog/2017/09/25/… – Lefthand

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". – Koby

@Lefthand "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. – Koby

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 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;

Trickish