put LIMIT <chunk_size> in google cloud spanner delete query

Question

my goal is to delete recrods in batch where I have a conditional query:

while(select * from <table_name> where <condition_1> limit 1;) exists,

delete from <table_name> where <condition_1> limit <chunk_size>;

using chunk_size to avoid deleting numerous rows in a single call.

Since spanner deletes using Mutation.delete(tableName, keySet) instead of a DML query like delete from <table_name> where . . . limit <chunk_size>. I'm wondering if there's a spanner way to put the limit <chunk_size> or alternate way to achieve the goal I mentioned above.

(I'm using scala, thus spanner java client)

Answer 1

To delete rows based on a non key criteria you currently need to do this in two steps.

First run a query: Select <PK columns> FROM <table_name> WHERE <condition_1> LIMIT <return_qualifying_rows_count>;

Then in the second step you use the returned primary key(s) in the Mutation.delete(tableName, keySet) function

If you want to delete in batch, set the <return_qualifying_rows_count> to the number of rows you want to delete in batch. There is no limit on how many rows you can delete in one call.