Update / Delete queries with where clause

SQL++
1

Hi,

Is it advisable to have update / delete N1QL queries with where clause?
Are there any concerns or limitations?

Thanks,
Igor Sandler

2

UPDATE/DELETE are mutations. If you can eliminate mutations system load will be less and scales well.
Only way to control mutations are through WHERE clause.

3

Ok, I will be more specific. What is preferable?

  1. Run update / delete with where clause.
  2. Iterate through the documents in the application and update/delete them one by one.

Thanks
Igor

4

Depends on situations if you know the keys you can use 2.

5

What if i need to select the keys using N1QL query?

6

It all depends on situation?
Can you create covering index (without that you need to do Fetch). UPDATE can’t be covered need to do Fetch.
If Yes
You can do DELETE through N1QL because DELETE you need only dockeys. Instead of getting keys and doing from client. If you think that is better you can do that.
If NO
N1QL getting document and UPDATE getting document twice in N1QL /client.

You can experiment and try it. You can also consider sub-doc update from the client.

7

I understand the DELETE logic, but UPDATE one is still not clear for me. Could you please elaborate about ‘N1QL getting document and UPDATE getting document twice in N1QL /client.’

8

To get document keys
SELECT META().id FROM default WHERE x = 5 AND y > 6;

If above query is covered , SELECT will not do Fetch and you can update through SDKs which does Fetch and update
so document got only once.

If above query is not covered , SELECT will do Fetch and you can update through SDKs which does Fetch and update
so document got twice (one in SELECT and another in SDK).

Similar UPDATE query looks
UPDATE default SET x = 20 WHERE x = 5 AND y > 6;

This query will never covered, One Fetch is done by update.

 Depends on  situation you can try it. If you do in SDK you may be able to do asynchronously multiple threads.
9

Does it mean that
UPDATE default SET x = 20 WHERE x = 5 AND y > 6;
will fetch the document only once?

10

UPDATE use indexscan produce document keys, fetch the document and re-evaluate predicate and and updates it.

11

I see, thanks.
Does it do it in parallel or at one query node / one thread ?

12

one query node one thread.

13

Thanks a lot !!!
Igor