Update with subquery for count


#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];

#5

Thank you! That works :slight_smile: