MERGE INTO not working as expected

N1QL
,
#1

Hello all,
I am new to Couchbase and learning N1QL. UPDATE and INSERT individually works okay. but when I try the MERGE INTO statement (Lookup merge with updates and inserts) , it performs update when matched on Key but does not perform insert when not matched.
This is the query I am using:
MERGE INTO travel-sample t
USING [
{“Age”:“3”, “Name”: “Sophia”}
] source
ON KEY “15”
WHEN MATCHED THEN
UPDATE SET t.Age = source.Age
WHEN NOT MATCHED THEN
INSERT ({ “Name”: source.Name, “Age”: source.Age});

Please let me know where I might be going wrong.

Thanks.

#2

It works for me

MERGE INTO default t USING [ {"Age":"3", "Name": "Sophia"} ] source ON KEY "15"
WHEN MATCHED THEN UPDATE SET t.Age = source.Age
WHEN NOT MATCHED THEN INSERT ({ "Name": source.Name, "Age": source.Age});

Also check out https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/

#3

This is still not working for me. Also I tried using the query described in the document (https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/) but it gives me error.
The query I used:

MERGE INTO travel-sample AS route USING travel-sample AS airport ON route.sourceairport = airport.faa AND airport.type = “airport” AND route.type = “route” WHEN NOT MATCHED THEN INSERT (KEY UUID(), VALUE {“sourceairport”: airport.faa, “targetairport”: “SFO”, “type”: “route”}) WHERE airport.country = “France”;

Error generated:

[
{
“code”: 3000,
“msg”: “syntax error - at route”,
“query_from_user”: “MERGE INTO travel-sample AS route \n USING travel-sample AS airport ON route.sourceairport = airport.faa \n AND airport.type = “airport” AND route.type = “route” \n WHEN NOT MATCHED THEN \n INSERT (KEY UUID(), VALUE {“sourceairport”: airport.faa, “targetairport”: “SFO”, “type”: “route”}) \n WHERE airport.country = “France”;”
}
]

#4 
 MERGE INTO `travel-sample` AS route
 USING `travel-sample` AS airport 
 ON route.sourceairport = airport.faa AND airport.type = "airport" AND route.type = "route" 
 WHEN NOT MATCHED THEN INSERT (KEY UUID(), VALUE {"sourceairport": airport.faa, "targetairport": "SFO", "type": "route"}) WHERE airport.country = "France";
#5

Yes. I used the same query. I think the problem is at ON Clause . ON KEY clause worked for update but didn’t work for insert. The ANSI MERRGE ON clause does not work for either update or insert.

#6

ANSI MERGE required CB 6.5.0, older versions use Lookup merge and will work;

SELECT * FROM default  USE KEYS "15";
MERGE INTO default t USING [ {"Age":"3", "Name": "Sophia"} ] source ON KEY "15"
WHEN MATCHED THEN UPDATE SET t.Age = source.Age
WHEN NOT MATCHED THEN INSERT ({ "Name": source.Name, "Age": source.Age})  RETURNING META(t).id, t.*;
SELECT * FROM default  USE KEYS "15";
#7

That was my understanding too. Currently I am using CB 6.0.4 and therefore just trying Lookup Merge. However the Insert is still now working and it doesn’t even generate any errors. Is there a way to check what I might be missing ?

#8

It should work paste complete out of the both commands.
First check key it is not present, then do merge and check key it should present

SELECT * FROM default  USE KEYS "15";
MERGE INTO default t USING [ {"Age":"3", "Name": "Sophia"} ] source ON KEY "15"
WHEN MATCHED THEN UPDATE SET t.Age = source.Age
WHEN NOT MATCHED THEN INSERT ({ "Name": source.Name, "Age": source.Age})  RETURNING META(t).id, t.*;
SELECT * FROM default  USE KEYS "15";
#9

worked. Thanks for the help. There was a document with key 15 that I missed and therefore every time it was just updating that. The Select statement helped identify the document.