Search code examples
couchbasesql++

N1QL update document with subquery

I'm trying to denormalise a "Country" document with "regionName" field corresponding to "name" of another "Region" document. My N1QL query is as below but it's not working

update test as t1 set regionName = (
 select raw name from test as t2 where `_class`="Region"
) where `_class`="Country" and t1.regionCode=t2.code RETURNING *;

Any help?

Solution

  • N1QL doesn't have UPDATE JOINS. You can't USE SET CLAUSE subquery source in UPDATE WHERE clause.

    Use Merge

    MERGE test AS m 
USING test AS s
ON s.`_class`="Region" AND m.`_class`="Country" AND m.regionCode=s.code
WHEN MATCHED THEN m.regionName = s.name;

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

    CB 6.0

    MERGE test AS m 
USING  (SELECT META(c).id, r.name
        FROM test AS r
        JOIN test AS c 
        ON r.`_class`="Region" AND c.`_class`="Country" 
           AND r.regionCode = c.code) AS s
ON KEY s.id
WHEN MATCHED THEN m.regionName = s.name;