Hooli Hooli - 4 months ago 25
Apache Configuration Question

UPDATE WHERE in MERGE

I'm trying to run the below query but I keep getting back:

Error code 30000, SQL state 42X01: Syntax error: Encountered "WHERE" at line 5, column 38.

MERGE INTO FOO
USING BAR
ON FOO."NAME" = BAR."NAME"
WHEN MATCHED THEN
UPDATE SET FOO."NAME" = 'qwerty' WHERE FOO."AGE" > 30
WHEN NOT MATCHED THEN
INSERT (guid) values('qwerty')


If I remove
WHERE FOO."AGE" > 30
then it seems to be fine though. Is it not possible to specify a
WHERE
clause in the
UPDATE
of a
MERGE
?

I'm using the latest apache derby embedded database version,
10.12.1.1

Answer

Can you try this:

MERGE INTO FOO
USING BAR
ON FOO."NAME" = BAR."NAME"
AND FOO."AGE" > 30
WHEN MATCHED THEN
    UPDATE SET FOO."NAME" = 'qwerty'
WHEN NOT MATCHED THEN
    INSERT (guid) values('qwerty')   
Comments