MERGE statement

Couchbase Server
1

Hi, I have a doubt about merge statement, I need update the record when EXISTS and its status is NOT resolved and LastUpdate < newRecord.lastUpdate , and INSERT when NOT EXISTS.

the query is like this:

merge into mybucket
using (select id from mybucket where lastUpdate < newRecord.lastUpdate and status != “RESOLVED”)
when matched then UPDATE SET…

I think that to INSERT it should comply whitn the ONLY condition IS NOT EXIST.

I appreciate your help.
Regards

2

Add WHEN NOT MATCHED THEN INSERT …

Also, change SELECT id

to SELECT META().id

3

Hi @geraldss , if I add this, verifies that not matched with condition used in USING clause, and I want to insert ONLY if not exists the document.

regards

4

Ok, got it.

MERGE INTO ...
USING ...
WHEN MATCHED THEN UPDATE ... WHERE ...
WHEN NOT MATCHED THEN INSERT ...

Use the WHERE clause in the UPDATE clause instead of putting the WHERE condition in the USING subquery.

5

Great, exist any method in couchbase SDK to use a MERGE statement ?

6

@daschl @prasad do we have MERGE in the SDKs?

7

Hello, something news? @daschl @prasad @geraldss

8

Hello, I have this query but it never insert the document.
when matched the document is updated correctly , but when not matched never inserts the document.

Where is the problem?

MERGE INTO mybucket b
USING (select meta().id from APP_mybucket_DATA use keys ‘key’ ) as xxx ON KEY xxx.id
WHEN MATCHED THEN
update set b.name = 'C3CC’
WHEN NOT MATCHED THEN
insert {“documentType”:“cccc”,“bases”:[],“createTime”:…}

thanks

9

Because it always matches. Try using a key that is present in APP_mybucket_DATA but absent in mybucket.

10

sorry, mybucket and APP_mybucket_DATA is the same bucket. Error when i typed

regards

11

Then by definition, everything will match.

12

Hi @rodrigo.rio,
which SDK r u using? As per following documentation, the n1ql API typically takes the query as string, which can be any n1ql statement (including MERGE). Pls refer to specific SDK for exact API availability/details (Java has it).
http://developer.couchbase.com/documentation/server/4.5/sdk/java/n1ql-queries-with-sdk.html
http://docs.couchbase.com/sdk-api/couchbase-java-client-2.3.4/

-Prasad

13

All SDKs have an option for executing an arbitrary String, so it’s always possible to execute any statement that can be assembled by String alone. Some SDKs have additional platform specific APIs (e.g. the DSL in Java or LINQ in .NET), but we ensure that the String option is always there.

The developer documentation has a section on N1QL from the SDK. Select the platform of interest from the pulldown in the upper right navigation.

1 Like