N1QL how to update with result from subquery?

SQL++
1

I want to update the image of the products based on product with the same name. Here’s my query:

UPDATE ipratico_store AS p SET p.image = (
    SELECT p2.image FROM ipratico_store AS p2
    WHERE p2.type = "product" AND p2.image IS VALUED AND p2.name = p.name)[0]
WHERE p.type = "product" AND p.image IS NOT VALUED

This is the error I get: Error evaluating SET clause. - cause: FROM in correlated subquery must have USE KEYS clause: FROM ipratico_store
I’ve tried to put USE KEYS p.name but it’s not working

2

Do you have relation from name to document key? If not this is not possible.
Your query does random update if there are duplicate updates or does self update is that you want?

Other option is use ARRAY collection.

UPDATE ipratico_store AS p SET p.image = (FIRST v.image FOR v IN (
    SELECT p2.image, p2.name FROM ipratico_store AS p2
    WHERE p2.type = "product" AND p2.image IS VALUED) WHEN v.name = p.name END)
WHERE p.type = "product" AND p.image IS NOT VALUED;
3

No, there is no relation between name and the document key, but thank you! Your “array collection” solution did work!
The only thing is that in this case the subquery is very heavy, because it loads all the documents and then keeps just the ones that has v.name = p.name. What I was trying to do is to pass the name in the subquery to only load the docs with that name. But never mind, because it is just a one time query.