Derby Merge insert not working

Question

I wanted to create a SQL query which is working like INSERT IF NOT EXISTS ELSE UPDATE

I found that Derby is capable of MERGE and i tried to use it to solve my issue.

MERGE INTO test_table a 
USING test_table b 
ON a.city = 'foo'
WHEN NOT MATCHED THEN INSERT values ( 'foo', '2012-11-11', 'UK')
WHEN MATCHED AND a.modification_date > '1111-11-11' THEN 
      UPDATE SET a.modification_date = '2012-11-11',
                 a.city = 'foo1', 
                 a.country = 'US'

The above statement is giving me the following error:

Error code 30000, SQL state 23505: The statement was aborted because it
would have caused a duplicate key value in a unique or primary key 
constraint or unique index identified by 'SQL150129144920080' defined on 'test_table'

How ever i can run the following statement:

INSERT INTO test_table values ( 'foo', '2012-11-11', 'UK');

Which is proving that the above city does not exists in the table yet.

My table is contains the following structure:

CREATE TABLE test_table(
       city VARCHAR(100) NOT NULL PRIMARY KEY,
       modification_date DATE NOT NULL,
       country VARCHAR(2) NOT NULL);

Any help or advice is greatly appreciated.

Answer 1

You have missed the following sentence from here

"The unqualified source table name (or its correlation name) may not be the same as the unqualified target table name (or its correlation name)."

that means you cannot use one table as source and target at the same time !

            just one example: 
            we have two schemas: schema1 and schema2 
            and two tables: schema1.table1 and schema2.table1 
            --i have to write all details: 
            create schema schema1; 
            create table schema1.table1 (
              name varchar(255) not null, 
              id int not null primary key 
            ); 

            create schema schema2; 
            create table schema2.table1 (
                name varchar(255) not null, 
              id int not null primary key 
            ); 


            --suppose we have inserted  some entries into schema2.table1 
            insert into schema2.table1 values 
            ('foo', 1), ('bar', 2); 

            --and we want just to copy values from schema2.table1 into schema1.table1 
            --apply MERGE INTO ... INSERT ... 
            merge into schema1.table1 as tableTarget 
            using schema2.table1 as tableSrc 
            on tableTarget.id= tableSrc.id 
            when matched then 
             update set tableTarget.name=tableSrc.name 
             when not matched then 
              insert(name, id) values (tableSrc.name, tableSrc.id); 

              --that has to work