SQL like subqueries

SQL++
1

One thing I don’t get (and which really annoys me) about N1QL is my inability to write subqueries like in SQL

For example, how would I go about writing something like:

UPDATE bucket a SET some_value = (SELECT COUNT(*) FROM bucket b WHERE a.some_other_value > a.some_value)

Without having a correlation using a document id?

This is just a basic example but also applies to updates etc.

I have read the docs but couldn’t find any working example for this case (especially in updates since you can’t use joins)

Regards,

Eric

2

At present correlated SUB queries must required USE KEYS.

This may not efficient but you can use JOIN and generate array of results and then use ARRAY collection to match and update.

UPDATE bucket a 
SET a.some_value =  FIRST v.cnt FOR v IN 
                             (SELECT COUNT(1) AS cnt, c.some_value 
                               FROM bucket b JOIN bucket c 
                               ON b.some_value > c.somevalue 
                               WHERE .....
                              GROUP BY c.some_value)
                    WHEN a.some_value = v.some_value END;
1 Like