N1QL MERGE - Multiple updates to a document

SQL++
1

With MERGE Statement, Will i be able to make multiple updates to the same document?
e.g: MERGE INTO target d
USING (select “Customer::”|| l.client doc_id, l.* from source l where meta(l).id like “ClientAddress::%”) s on key s.doc_id
WHEN MATCHED THEN
UPDATE SET
d.addresses = OBJECT_PUT(d.addresses, s.zipcode,{
“line1”: s.line1,
“line2”: s.line2,
“state”: s.state,
“zipcode”: s.zipcode
});
Here, using multiple ClientAddress documents in thw source, i want to update the same Client document. But When i run this query, I am getting the following error
{
“code”: 5320,
“msg”: “Multiple UPDATE/DELETE of the same document (document key ‘Customer::490’) in a MERGE statement”
}.

Is there a way to get it done?

2

Make your subquery return unique documents.

SELECT  "Customer::"|| l.client doc_id,MAX(l).*  
FROM source  AS l 
WHERE meta(l).id LIKE  "ClientAddress::%"
GROUP BY l.client
3

I know that will avoid the error. But i want data from hoth the customeraddress documents to be available in the customer document. Consider that each address has different zipcode.

4

Target table document can’t be updated twice in same statement. You need to do in one operation.

MERGE INTO target d
USING (SELECT "Customer::"|| l.client AS doc_id, ARRAY_AGG({l.line1, l.line2, l.state, l.zipcode}) AS doc
       FROM source AS l
       WHERE META(l).id LIKE "ClientAddress::%"
       GROUP BY "Customer::"|| l.client
       ) AS s
ON KEY s.doc_id
WHEN MATCHED THEN
UPDATE SET d.addresses = OBJECT_CONCAT(IFMISSINGORNULL(d.addresses,{}), OBJECT v.zipcode: v FOR v IN s.doc END);
1 Like
5

Wow… That works perfect… Thanks a lot…