How to fix couchbase update sql with cte which is giving error?

Couchbase Server
,
1

Please get detail from here.

2

Only SELECT can use CTEs. Not UPDATE.

3

Use Merge statement MERGE | Couchbase Docs You may have to create right index on target table

MERGE INTO xyz AS x
USING (WITH abc AS ( ...)
       SELECT .... ) AS sq_filter
ON x.aId = sq_filter.aId
  AND x.tn = sq_filter.tn
  AND x.cti = "aa"
WHEN MATCHED THEN UPDATE SET x.blocks.up = ARRAY v FOR v IN x.blocks.up WHEN v.bId != sq_filter.bId END;

If sq_filter matches same target multiple times

MERGE INTO xyz AS x
USING (WITH abc AS ( ...)
       SELECT aId, tn, ARRAY_AGG(bId) bIds
       FROM ......
       GROUP BY aId, tn) AS sq_filter
ON x.aId = sq_filter.aId
  AND x.tn = sq_filter.tn
  AND x.cti = "aa"
WHEN MATCHED THEN UPDATE SET x.blocks.up = ARRAY v FOR v IN x.blocks.up WHEN v.bId NOT IN sq_filter.bIds END;
1 Like
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.