Insert to table or Update if exist in Worklight Database Adapter

Question

I use IBM Worklight Studio 6.1 to develop my hybrid application and use Apache Derby as my SQL. The problem is I want to insert to the table or update if the key is exist in the database. I got the answer from this question: Insert to table or update if exists (MySQL). It works in SQL Fiddle.

But when I use it in my SQL Adapter, it show me this error.

{
    "errors": [   
        "Runtime: Syntax error: Encountered \"ON\" at line 1, 
        column 71..\nPerformed query:\n
        INSERT INTO ACC_ID_KEY (PREFIXKEY, ELEMENT) \t\tVALUES (?, 0) 
        ON DUPLICATE KEY UPDATE ELEMENT = VALUES(ELEMENT) + 1"   
    ],
    "info": [
    ],
    "isSuccessful": false,
    "warnings": [
    ]
}

I do not know what happens...

Ps. I try to change ELEMENT = VALUES(ELEMENT) + 1 to ELEMENT = ELEMENT + 1 but it still show me the same error
Ps2. I try this code, but it still not working

IF(( SELECT A.ELEMENT
    FROM ACC_ID_KEY A
    WHERE A.PREFIXKEY = 'A01407') IS NULL)

BEGIN
    INSERT INTO ACC_ID_KEY (PREFIXKEY, ELEMENT)
    VALUES ('A01407', 1)
END

ELSE
    BEGIN
        UPDATE ACC_ID_KEY
        SET ELEMENT = ELEMENT + 1
        WHERE PREFIXKEY = 'A01407'
    END

Ps3. Also this code too

CASE
WHEN (SELECT A.ELEMENT
    FROM ACC_ID_KEY A
    WHERE A.PREFIXKEY = 'A01407') IS NULL
THEN INSERT INTO ACC_ID_KEY (PREFIXKEY, ELEMENT)
    VALUES ('A01407', 1)
ELSE UPDATE ACC_ID_KEY
    SET ELEMENT = ELEMENT + 1
    WHERE PREFIXKEY = 'A01407'
END

(I found that Apache Derby can use the CASE statement from Here. I use the code from the example

CASE
    WHEN 1 = 2 THEN 3
    WHEN 4 = 5 THEN 6
ELSE 7
END

but the Adapter still show me the error at the "CASE" statement)

score 2 · Answer 1 · answered Jul 07 '14 at 10:13

INSERT OR UPDATE or INSERT OR IGNORE -like syntax is not standard SQL and is not supported by Derby.

The latest Derby trunk (not yet released) contains the SQL MERGE statement which allows you to accomplish many of the same tasks, but with a rather different syntax (explained in the alpha manuals).

A release containing these features (10.11) is expected in early fall.

A workround until then is something like this...

INSERT INTO T VALUES ... IF NOT EXISTS ( SELECT ... FROM T WHERE <k> = <value> )
UPDATE T SET <col> = CASE WHEN ...

it becomes more tricky with primary key updates

Insert to table or Update if exist in Worklight Database Adapter

1 Answers1