Geek asks and answers

Hsqldb update on insert

Does anyone know of a solution to make HSQLDB update columns by calling INSERT. I would like my code to be able to embed, and if there is data, update this data. I know that MySQl, I believe, has a "DUPLICATE KEY UPDATE". I can't seem to find recent documentation.

+5
source share
2 answers

A good selection is sometimes better than the official documentation for the MERGE manual :)

Example 1

For a table ( MY_TABLE) with columns ( COL_Aand COL_B), where the first column is the primary key:

MERGE INTO MY_TABLE AS t USING (VALUES(?,?)) AS vals(a,b) 
        ON t.COL_A=vals.a
    WHEN MATCHED THEN UPDATE SET t.COL_B=vals.b
    WHEN NOT MATCHED THEN INSERT VALUES vals.a, vals.b

Example 2

Add another column ( COL_C) to our table:

MERGE INTO MY_TABLE AS t USING (VALUES(?,?,?)) AS vals(a,b,c) 
        ON t.COL_A=vals.a
    WHEN MATCHED THEN UPDATE SET t.COL_B=vals.b, t.COL_C=vals.c
    WHEN NOT MATCHED THEN INSERT VALUES vals.a, vals.b, vals.c

Example 3

(COL_A COL_B):

MERGE INTO MY_TABLE AS t USING (VALUES(?,?,?)) AS vals(a,b,c) 
        ON t.COL_A=vals.a AND t.COL_B=vals.b
    WHEN MATCHED THEN UPDATE SET t.COL_C=vals.c
    WHEN NOT MATCHED THEN INSERT VALUES vals.a, vals.b, vals.c

!

+9

More articles:


All Articles