4

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?

Community
  • 1
  • 1
Jon Martin Solaas
  • 737
  • 1
  • 10
  • 19
  • 1
    Please post some sample input and your desired output.. Also write the complete query... – Teja Mar 02 '12 at 15:39

1 Answers1

8

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 ...)
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • Thanks, I guess I'll just drop h2 for this, unless db2 (Z-Os) has the same limitations? – Jon Martin Solaas Mar 02 '12 at 16:43
  • DB2 (any version) has the same limitations, and doesn't have the `MERGE` statement either. – Clockwork-Muse Mar 02 '12 at 16:58
  • DB2 does indeed have the `MERGE` statement. It's on [z/OS since 9.1](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.sqlref/src/tpc/db2z_sql_merge.htm), and [Linux/Unix/Windows since at least 9.5](http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0010873.html) (possibly earlier). It's not the same syntax as above, though... – bhamby Mar 02 '12 at 17:55
  • Exellent, merge it is, then. Thanks a bunch! – Jon Martin Solaas Mar 02 '12 at 18:52
  • @galador http://database.ittoolbox.com/groups/technical-functional/db2zos-l/db2v9-zos-merge-from-select-4636150 Seems merge isn't available on z/OS until v. 10. See reply from Michael Senatore. I only get ILLEGAL USE OF KEYWORD SELECT. TOKEN VALUES WAS EXPECTED SQL Code: -199, SQL State: 42601 ... – Jon Martin Solaas Mar 07 '12 at 13:11
  • Merge is indeed in v9 (see my link above). If you're on version 9 and cannot use Merge, then you may not yet be in [new function mode](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.inst/src/tpc/db2z_migrdb2.htm). I will note however, that you cannot do a Merge using a `SELECT FROM` on z/OS, if you need to merge more than one row, you must use [host variable arrays](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_hostvariablearray.htm). – bhamby Mar 07 '12 at 14:02
  • I don't think the DB2 Merge is compatible with the H2 Merge statement (that's why I wrote 'the non-standard MERGE statement' above). – Thomas Mueller Mar 07 '12 at 19:26
  • @ThomasMueller I agree! (And mentioned that in my first reply.) – bhamby Mar 07 '12 at 22:59