Subquery doesn't work nested in update

SQL++
,
1

When trying those update and select query separetedly, they are working fine, but when nested, i got error.

Query:
UPDATE bucket as p
SET
p.content.customer = (select t.content.lastUpdateDate from bucket as t where t.type=‘contact’ and t.content.code = p.content.code)
WHERE p.type = ‘contact’ AND p.content.code = ‘verif_code_007’
error:
[
{
“code”: 5370,
“msg”: “Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM bucket.”,
“query_from_user”: “UPDATE bucket as p\r\nSET\r\np.content.customer = (select t.content.lastUpdateDate from rxpData as t where t.type=‘contact’ and t.content.code = p.content.code)\r\nWHERE p.type = ‘contact’ AND p.content.code = ‘verif_code_oo7’”
},
{
“code”: 5010,
“msg”: “Error evaluating SET clause. - cause: FROM in correlated subquery must have USE KEYS clause: FROM bucket.”
}
]
I don’t understand at all what is it mean. Please help me!

2

Track this via MB-30813

Alternative MERGE https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/

MERGE INTO bucket AS p USING bucket AS t 
ON t.type="contact"  AND   p.type="contact" 
         AND t.content.code = p.content.code 
        AND  p.content.code = "verif_code_007" AND  t.content.code = "verif_code_007" 
WHEN MATCHED THEN UPDATE   SET p.content.customer  =  t.content.lastUpdateDate;

don’t data model and logic if works for you

UPDATE bucket as p
SET p.content.customer =  p.content.lastUpdateDate
WHERE p.type = ‘contact’ AND p.content.code = ‘verif_code_007’
5

I tried what you have just send to me, but unfortunatly it doesn’t work at all with this error:
It doesn’t work at all
[
{
“code”: 3000,
“msg”: “syntax error - at t”,
}
]

6

ANSI merge is supported in 6.5+

7

Sorry, i am using 5.5 and the version can’t be changed. Any solution for me?

8

don’t know data model and logic what i can see u want update another field in document from field from the same document in that case you don’t need subquery.

Tell us what exactly you need with sample documents, how many documents query qualifies will see any solution.

9

The exact thing i want to do is to use a parent parameter in his subquery!

10

This is where my blocking point is!

11

This is it:
UPDATE myBucket as p
SET
p.content.customer = (SELECT bao.customerId, bao.canalName FROM subQuery1 as bao where any t in bao.linkedContact satisfies p.content.code = t end)
WHERE p.type = ‘contact’ AND p.content.code IN (subQuery2);

The real matter is in using p.content.code in the select while he is refering from the parent (update)

12

If you need parent parameter in subquery you must use USE KEYS as mentioned in MB-30813

If result set is small and equivalent query of your query is (it may need more memory and time)
Use non-correlated subquery and materialize into ARRAY then walk through ARRAY construct new one by matching the parent condition.

UPDATE bucket as p
SET p.content.customer =
ARRAY {v.lastUpdateDate} FOR v IN (select t.content.lastUpdateDate, t.content.code from bucket as t where t.type=‘contact’ ) WHEN v.code = p.content.code END
WHERE p.type = ‘contact’ AND p.content.code = ‘verif_code_007’

13

I tried to use USE KEYS as mentioned, but it doesn’t work as espected, displaying no error but doesn’t work as it should be. May be i use it the wrong way. How should i use it in this specific case:
UPDATE myBucket as p
SET
p.content.customer = (SELECT bao.customerId, bao.canalName FROM subQuery1 as bao where any t in bao.linkedContact satisfies p.content.code = t end)
WHERE p.type = ‘contact’ AND p.content.code IN (subQuery2)