[Solved] MERGE INTO

[dreamquartz]

Hi All,

Trying to figure out how 2 use MERGE INTO.
To simplify the database, I want to combine the table tPostalCode with the table tAddress., with their relationship based on

Code: Select all

"tAddress"."FKPostalCodeID" = "tPostalCode"."PostalCodeID"

I cannot find any information in the manual (HSQLBD 2.5.1) where a MERGE INTO from table to table is explained.
There is some knowledge on the internet, but nothing related to HSQLDB, that is useful.

Notes:
- During development it was decided that every person entered in the database will have an Address, even if the Address in 'unknown'. This leads to:
- tAddress holds a lot of Addresses for which there are no PostalCoses known, and therefore the value of KFPostalCodeID = NULL.
- tAddress was copied into tAddressPLUSPostalCode for development reasons

Code: Select all

INSERT INTO "tAddressPLUSPostalCode" ( "PostalCode" )
SELECT "PostalCode"
 FROM "tPostalCode", "tAddressPLUSPostalCode"
WHERE "tAddressPLUSPostalCode"."FKPostalCodeID" = "tPostalCode"."PostalCodeID"

This above code works for INSERT INTO, however trying to convert it to MERGE INTO leads to errors galore.
The latest one is: 1: user lacks privilege or object not found: tPostalCode.PostalCodeID

This is what I came up with so far:

Code: Select all

MERGE INTO "tAddressPLUSPostalCode" USING
     (
           SELECT
                "PostalCodeID", "PostalCode"
           FROM
               "tPostalCode"
      ) AS vals(x,y) ON
     (
           "tAddressPLUSPostalCode"."FKPostalCodeID" = "tPostalCode"."PostalCodeID"
     )
WHEN MATCHED THEN
       UPDATE SET "tAddressPLUSPostalCode"."PostalCode" = "tPostalCode"."PostalCode"
       WHERE "tAddressPLUSPostalCode"."FKPostaclCodeID" = "tPostalCode"."PostalCodeID"
WHEN NOT MATCHED INSERT VALUES ( NULL) 

Does anyone have any suggestions on how to proceed to move more than 50,000 PostalCodes into tAddress?

Your help is really appreciated,

Dream

[dreamquartz]

Finally found an answer.

Code: Select all

MERGE INTO "tAddressPLUSPostalCode" "tAPPC"
	USING (SELECT "PostalCodeID", "PostalCode" FROM "tPostalCode" ) "tPC"
		ON ("tAPPC"."FKPostalCodeID" = "tPC"."PostalCodeID" )
	WHEN MATCHED THEN
		UPDATE SET "tAPPC"."PostalCode" = "tPC"."PostalCode" 
	WHEN NOT MATCHED THEN
		INSERT("PostalCode")
		VALUES("PostalCode")

Dream