Couchbase query throw error 3000 when trying to delete entries using below query

Lior Cohen 0
Viewed 26

can you assist and explain why below query throw error on Couchbase:

DELETE FROM runtime
WHERE id, documentType IN (SELECT id, documentType FROM runtime
                           WHERE id IS NOT MISSING
                                 AND documentType IS NOT MISSING
                           GROUP BY id, documentType
                           HAVING COUNT(*) > 1);

the inner query within the parenthesis works without issues.

n1ql couchbase
1 Answers

IN clause doesn't support tuple based syntax, you can use as array (convert values both side into singe value using ARRAY). Also subquery returns array of OBJECTS, right side of IN clause is subquery use RAW to remove OBJECT.

DELETE FROM runtime 
WHERE [id, documentType] IN (SELECT RAW [id, documentType]
                             FROM runtime 
                             WHERE id IS NOT MISSING 
                                 AND documentType IS NOT MISSING 
                             GROUP BY id, documentType 
                             HAVING COUNT(1) > 1);

You can also use the following based on which performs better

CREATE INDEX ix1 ON runtime(documentType, id);

MERGE INTO runtime AS m USING (SELECT id, documentType
                               FROM runtime AS r 
                               WHERE id IS NOT MISSING 
                                    AND documentType IS NOT MISSING
                               GROUP BY id, documentType
                               HAVING COUNT(1) > 1) AS s
ON m.id = s.id AND m.documentType = s.documentType
WHEN MATCHED THEN DELETE;
vsr0
Related