H2更新并加入 [英] H2 update with join

问题描述

作为开发数据库，​​我正在使用MySQL，而对于测试，我正在使用H2数据库. 以下脚本在MySQL中运行良好，但在H2上失败.

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

在互联网上，我发现h2不支持JOIN的UPDATE子句.也许有一种不用JOIN子句重写此脚本的方法吗?

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?

顺便说一句，我正在使用liquibase.也许我可以用它的xml语言编写UPDATE子句?

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

我尝试了以下脚本

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

但是我仍然遇到错误.似乎H2不支持在一个查询中更新多个表.如何在两个不同的查询中重写此查询以收集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?

推荐答案

尝试如下操作:

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)

这篇关于H2更新并加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！