Update values in one table from three other joined tables in H2 (and DB2)

Question

I'm trying to achieve something like described here in H2:

Update with inner join?

update tlegacy lca set lca.pr_dato = ca.calc_holdings_date
... from tca ca inner join tdd dd on ...

and I get Error: Column "CA.CALC_HOLDINGS_DATE" not found in H2.

The "missing" field is ofcourse present. I've tried a number of variants with no luck. Does H2 support this way of updating values in one table collected from a number of other joined tables? Eventually this should run on IBM DB2. Is it supported there?

Answer 1

For H2 there are two options. The first one will work for all databases:

update tlegacy lca set 
  lca.pr_dato = (select ca.calc_holdings_date ... from tca ca where ...)
  where lca.id in (select ca.id from tca where ...)

The second options is using the non-standard MERGE statement. It will insert new rows if no row with this key exists yet.

merge into tlegacy(pr_dato) key(id) 
  select ca.calc_holdings_date, ca.id from tca ca where ...
  and exists (select * from tlegacy where ...)