CODEWITHSUNDEEP
sqlpostgresqljoinhsqldb
kostepanych
kostepanych

Reputation: 2619

HSQLDB update field of one table from field of another one

Trying to do this:

UPDATE table1 t1
SET cost = t2.price
FROM table2 t2
WHERE t1.item_id = t2.item_id

It's working on PostgreSQL but not working on HSQLDB.

How to fix this script to support working both on PostgreSQL and HSQLDB?

Upvotes: 0

Views: 4219

Answers (2)

fredt
fredt

Reputation: 24372

The UPDATE ... FROM syntax is non-standard. This is Standard SQL and works in HSQLDB (and apparently in PostgreSQL). I am really surprised that PostgreSQL 9.4 syntax documentation does not mention this syntax which has been in the Standard since 1992.

UPDATE table1 t1
SET cost = (SELCT t2.price
FROM table2 t2
WHERE t1.item_id = t2.item_id)

Upvotes: 5

wero
wero

Reputation: 33000

HSQLDB does not support UPDATE ... FROM statements. On HSQLDB use the MERGE statement instead. Out of my head, hoping this does not contain syntax errors:

 MERGE INTO table1 USING table2 ON table1.item_id = table2.item_id
    WHEN MATCHED THEN UPDATE SET table1.cost = table2.price

Unfortunately, as of today postgres does not seem to support the MERGE statement, but only the similar UPSERT.

Upvotes: 3

Related Questions