Update with subquery for count

SQL++
1

Hello,

I’m trying to see if this type of query is possible or not.

Update bucket bucket1 set total_comments = (select count(*) as total from bucket bucket2 where type='comment' and target_id = bucket1.id).

It does not work as expected :slight_smile: It looks like referencing bucket1 in the subquery is not supported.

Is this even possible?

2

Try the following:

Update bucket bucket1 set total_comments = (select raw count(*) as total from bucket bucket2 where type='comment' and target_id = bucket1.id)[0];

3

Thank you geraldss

I get "Error evaluating SET clause - cause: FROM in correlated sub query must have USE KEYS clause: FROM bucket"

4

Ok, getting closer. Please try the following:

CREATE INDEX idx_target ON mybucket(target_id);

UPDATE mybucket AS target
SET total_comments = (
    SELECT RAW COUNT(*) AS total
    FROM mybucket AS target2 USE KEYS META(target).id
    JOIN mybucket AS mycomments ON KEY mycomments.target_id FOR target2
    WHERE mycomments.type='comment'
)[0];
1 Like
5

Thank you! That works :slight_smile:

1 Like