Update field with select result (using Merge?)

SQL++
1

Hi there,

I’m a bit new to N1QL, and I’m struggling on a problem which looked very simple on basic SQL, and probably is on N1QL:
I have a Rest document on which I want to fill up a field, depending on data from another Rest document.
Basically, here’s what it looks like:

_class = ‘Intent’
=> fields name & meta.id

_class = ‘Journal’
=> field intent, which equals to Intent’s name, and intentId, which should be filled up with Intent’s meta.id

So here’s what the SQL query would look like, just to give you an idea of what I’m trying to achieve:

UPDATE bucket a SET a.intentId = (SELECT meta(c).id FROM bucket c WHERE c._class = 'Intent' AND c.name=a.intent) WHERE a._class= 'Journal';

I came up with something like this, but obviously it doesn’t work:

MERGE INTO bucket j USING (SELECT * FROM bucket WHERE _class = 'Intent') AS i ON KEY i.name = j.intent AND j._class = 'Journal' WHEN MATCHED THEN UPDATE SET j.intentId=meta(i).id;

And I get this :

[ { "code": 5030, "msg": "Invalid MERGE key <nil> of type <nil>." } ]

I’m a bit confused about how the ON KEY works…

2

OK KEY takes a record id, not a joiin expression. Each record in the USING expression needs to contain a field that id the record id of the record that should be changed. You then name this field in the ON KEY clause.

3

See the examples at: https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/merge.html

We don’t have generalized ON expression yet. We’re working on it.
Right now, the SOURCE (in this case orders) needs to have information on the document key of product. In the case below, o.productid has the document key of the product. (meta(product).id).

MERGE INTO product p USING orders o ON KEY o.productId
WHEN MATCHED THEN
     UPDATE SET p.lastSaleDate = o.orderDate
WHEN MATCHED THEN
     DELETE WHERE p.inventoryCount  <= 0
4

You need to have relation through document key as mentioned by @johan_larson, @keshav_m. You can include the relation and try it.

Other option is execute two independent queries and join through array.

CREATE INDEX ix1 ON bucket(name) WHERE _class = 'Intent';
UPDATE bucket a SET a.intentId =
FIRST v.id FOR v IN (SELECT meta(c).id, c.name FROM bucket c WHERE c._class = 'Intent') 
          WHEN  v.name=a.intent END
WHERE a._class= 'Journal';
2 Likes
5

Thanks a lot for your replies, I didn’t answer right away because I was working on something else.
In the end, I used vsr1 solution since this index was already created on this class, and it worked perfectly.

1 Like