Can I execute N1QL query in loop

N1QL
1

I have a bucket with size 500 K records.
I want to delete all the records using ‘Delete’ statement of N1QL.

I want to use ‘limit’ clause in the ‘Delete’ sql e.g.
Delete from Buckethelp limit 1000

Can I execute the above query in loop so that it will delete all 500 K records from the bucket.
Can I execute above query in loop on cbq prompt, or
Can I execute it like batch processing job like shell script.

2

Yes you can execute in the loop until mutationCount returns 0
cbq shell you can repeat statement
In shell script or SDKs where there is loop construct you can use REST API or SQL

3

Thanks vsr1 for the replay,

I was able to execute N1QL query in loop from a shell script,
I have saved that shell script in a text file and executed the file from cbq prompt.
Also I was able to execute a ‘delete’ query from cbq prompt.

After executing ‘delete’ query from cbq prompt it gives me ‘mutationCount’ in the result.
But I am not able to that ‘mutationCount’ in a variable so that it can be used for comparison in the
loop statement (i.e. how to loop until mutationCount returns 0)

Is there any property or any way so that I can get the ‘mutationCount’ returned by the delete query in the loop and that value I can compare to zero and exit loop when ‘mutationCount’ is zero

4

Depends on what type of shell script you used. In python you can store result in dictionary and get that value.

5

Thanks vsr1,

It worked with Python shell script.