CODEWITHSUNDEEP
javasqlpostgresqlhsqldbliquibase
Son Pham
Son Pham

Reputation: 13

How to transfer postgres script to hsqldb script?

I have a script, which I run successfully in postgres db, however it was failed when I run in hsqldb. Can someone help me change this sql to make it work for both HSQLDB and Postgres DB? Below is my script: 

UPDATE tableA af
SET columnA2 = b.columnB2
from
  ( select columnB1, columnB2 from.....) as b
Where af.columnA1 = b.columnB1;

This throws the following exception when I run it in hsqldb:

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: b.columnB2 Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: b.columnB2

Thanks.

Updated: I created another view to make my question more clear.

table_A has 2 columns: company_code, company_number and view_B has 2 columns: company_code, company_number_correct_answer

table_A has 10000 rows, and view_B has only 2 rows. What I want is updating 2 record in table_A, with company_code existing in view_B and set table_A.company_number = view_B.company_number_correct_answer

Upvotes: 0

Views: 249

Answers (2)

fredt
fredt

Reputation: 24352

You can also use MERGE in HSQLDB

MERGE INTO tableA af
USING (select columnB1, columnB2 from.....) as b
ON af.columnA1 = b.columnB1
WHEN MATCHED THEN
UPDATE SET af.columnA2 = b.columnB2

Upvotes: 0

user330315
user330315

Reputation:

In standard SQL, the FROM clause (or JOIN or similar things) is not valid for the UPDATE statement.

If you want an UPDATE statement that works across multiple database products, you will have to use a co-related sub-query:

update table_a
  set columna2 = (select columnb2
                  from table_b 
                  where table_b.columnb1 = table_a.columna1)
where exists (select * 
              from table_b 
              where table_b.columnb1 = table_a.columna1);

Note that this requires that table_b.columnb1 is a unique or primary key, otherwise the sub-query would return more than one row which will lead to an error).

Upvotes: 1

Related Questions