# Multiple rows delete: how to write a delete query **URL:** \ **Category:** Deletion\ **Tags:** deletion\ **Created:** [August 3, 2015, 12:25pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564 "2015-08-03T12:25:23Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![abhishekptrt](https://avatars.discourse-cdn.com/v4/letter/a/9de0a6/32.png) [@abhishekptrt](https://discuss.aerospike.com/u/abhishekptrt)\ **Post date:** [August 3, 2015, 12:25pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/1 "2015-08-03T12:25:23Z")
Hi all, I am new in aerospike . I have create a namespace say “Company” with in that i have set “Products” and bins are (data,k, product\_id, product\_type) I want to write the delete query a) delete all product of particular type. b) delete all product of particular id How should i write these query . i am using php client . thanks, ---
**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\ **Post date:** [August 3, 2015, 6:23pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/2 "2015-08-03T18:23:05Z")
You would use a [record UDF](https://www.aerospike.com/docs/guide/record_udf.html), written in Lua, to inspect whether the criteria are met for deletion, and if they are you’d call the [aerospike.remove()](https://www.aerospike.com/docs/udf/api/aerospike.html#aerospikeremove) function. Currently you have to use [scanApply()](https://github.com/aerospike/aerospike-client-php/blob/master/doc/aerospike_scanapply.md) to attach the record UDF to a scan of the set Company.Products. Be aware that we are working on secondary index record UDFs, meaning there will soon be a queryApply() that will let you run a record UDF against the records matched by a secondary index query. This should fit your use case better, as the query itself can be targeted at an index over product type or ID, rather than scanning all the records in the set. ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 18, 2017, 4:37am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/3 "2017-09-18T04:37:15Z")
Do you have an example for this topic ? ---
**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\ **Post date:** [September 18, 2017, 5:18am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/4 "2017-09-18T05:18:49Z")
I answered a [similar question](https://stackoverflow.com/questions/38500522/aerospike-delete-record-in-list-if-element-of-bin-known/38532195#38532195) on stackoverflow. ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 18, 2017, 6:47am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/5 "2017-09-18T06:47:34Z")
Thank you for your answer. After query, I have a RecordSet about 2M records, now I want to remove all these records . Can you tell me how I can do this with best performance ? This is my code to do this but it’s very slow (200s to remove 2M record). ``` AerospikeClient mAsClient = new AerospikeClient(Const.AEROSPIKE_HOST, Const.AEROSPIKE_PORT); mAsClient.register(null, "udf/aggregate.lua", "aggregate.lua", Language.LUA); Statement stmt1 = new Statement(); stmt1.setNamespace("test"); stmt1.setSetName("network"); stmt1.setBinNames("id"); RecordSet resultSet = mAsClient.query(null, stmt1); while (resultSet.next()) { String id = resultSet.getRecord().getString("id"); Statement stmt = new Statement(); stmt1.setNamespace("test"); stmt1.setSetName("network"); stmt.setFilters(Filter.equal("id", id)); ExecuteTask task = mAsClient.execute(null, stmt, "aggregate", "deleteRecord"); System.out.println("Status = " + task.queryStatus()); } ``` and my lua : ``` function deleteRecord(r) aerospike:remove(r) end ``` ---
**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\ **Post date:** [September 18, 2017, 6:52am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/6 "2017-09-18T06:52:14Z")
As I pointed out, you do not want to get a RecordSet, you want to apply a record UDF to each that matches your criteria. The UDF has a single line removing the record it’s being applied to. Here’s a [similar example](https://stackoverflow.com/questions/45193957/how-to-modify-the-ttl-of-all-records-set-with-a-ttl-of-1-in-aerospike) of modifying the TTL of all records that match a specific criteria. You would change the Lua code from trying to modify the TTL and update the record to an `aerospike:remove(rec)` call. ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 18, 2017, 7:00am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/7 "2017-09-18T07:00:29Z")
Thank you @zbotzer , I will try it. ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 18, 2017, 8:25am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/8 "2017-09-18T08:25:39Z")
I tried your example in my issue and it’s working perfect. Thank you so much . ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 26, 2017, 3:47am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/10 "2017-09-26T03:47:36Z")
Hello zbotzer, I removed records from aerospike by update TTL = 1 to record. But the records still on Ram and after few seconds( ~ 10s ) , the records have gone from Ram. I want after update TTL then record has gone immediate so how I can do that ? ---
**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\ **Post date:** [September 26, 2017, 5:07am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/11 "2017-09-26T05:07:57Z")
If you set the TTL to 1s, it will expire one second after being written. The record cannot be accessed by a get once it is expired, but it still needs the [nsup](https://discuss.aerospike.com/t/eviction-mechanisms-in-aerospike-3-8-and-onwards/2854) thread to clean it up from the primary index. It’ll consume the 64B of DRAM in the primary index until it’s removed by the namespace supervisor. Instead of changing the TTL to 1 second in order to remove a record, your code should call `aerospike:remove(rec)`. See the [aerospike module](http://www.aerospike.com/docs/udf/api/aerospike.html) of the [Lua UDF API](http://www.aerospike.com/docs/udf/api_reference.html). That will immediately remove the record’s metadata from the primary index. What you were doing before (rather than updating the TTL) should work. ---
**Author:** ![Jacky](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@Jacky](https://discuss.aerospike.com/u/Jacky)\ **Post date:** [September 26, 2017, 6:32am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/12 "2017-09-26T06:32:21Z")
Thanks rbotzer , After changed aerospike:update() to aerospike:remove() , it’s working perfect as I want . Thanks so much. ---
**Author:** ![Sharon\_Rosenfeld](https://avatars.discourse-cdn.com/v4/letter/s/ea666f/32.png) [@Sharon\_Rosenfeld](https://discuss.aerospike.com/u/Sharon_Rosenfeld)\ **Post date:** [July 12, 2018, 7:15am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/13 "2018-07-12T07:15:45Z")
hi ronen, is the queryapply on secondary index already in the release? ---
**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\ **Post date:** [July 14, 2018, 12:05am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/14 "2018-07-14T00:05:20Z")
Long time ago > Check out the latest PHP client, and generate the PHPDoc for yourself. ---
**Author:** ![alex75](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/alex75/32/910_2.png) [@alex75](https://discuss.aerospike.com/u/alex75)\ **Post date:** [August 1, 2018, 12:52pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/15 "2018-08-01T12:52:17Z")
Hi, don’t know if it is the case to open a new thread? I need to know the number of record deleted (removed) from the Set. I’m using this registered UDF: ``` function delete(r) aerospike: remove(r) end ``` In C# I call it using: ``` public void DeleteItems(IEnumerable itemIds) { foreach (var itemId in itemIds) { var statement = new Statement { Namespace = configuration.Namespace, SetName = configuration.ItemsSetName, Filter = Filter.Equal("Id", itemId) }; Client.Execute(null, statement, removeRecordFileName, removeRecordFunctionName); } } ``` My initial idea was to count the record before and after but seems like a SQL “select count(\*)” is not so simple to do in Aerospike. Any suggestion? ---
**Author:** ![Albot](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/albot/32/2076_2.png) [@Albot](https://discuss.aerospike.com/u/Albot)\ **Post date:** [August 2, 2018, 12:29am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/16 "2018-08-02T00:29:32Z")
You can just do a statement with includebindata=false and then invoke the execution. ``` var statement = new Statement { Namespace = configuration.Namespace, SetName = configuration.ItemsSetName, Filter = Filter.Equal("Id", itemId) }; QueryPolicy qp = new QueryPolicy(); qp.includeBinData=false; rs=Client.query(qp, statement,); while(rs.next()){ count++; } ``` or something similar. ---
**Author:** ![kmurakon](https://avatars.discourse-cdn.com/v4/letter/k/41988e/32.png) [@kmurakon](https://discuss.aerospike.com/u/kmurakon)\ **Post date:** [December 24, 2020, 7:34am UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/17 "2020-12-24T07:34:06Z")
How to delete a single record from AQL? Found below query from aerospike documentation but unable to get the PK of a record to delete. DELETE FROM [.] WHERE PK= ---
**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\ **Post date:** [December 24, 2020, 3:46pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/18 "2020-12-24T15:46:30Z")
PK is what is your key - when you created a record. Aerospike (at the client library) takes your key, set name (defualt null) and you key type inferred, and hashes to a digest in the client library. The client library sends only the digest to the Aerospike server by default. So all the server has is your record’s digest against which it looks up the record’s value or data. You have to know your key. So - DELETE FROM mynamespace.myset WHERE PK = “mykey” ---
**Author:** ![kmurakon](https://avatars.discourse-cdn.com/v4/letter/k/41988e/32.png) [@kmurakon](https://discuss.aerospike.com/u/kmurakon)\ **Post date:** [December 24, 2020, 4:48pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/19 "2020-12-24T16:48:24Z")
Thanks Piyush. We are trying to delete a record where we don’t know PK of it. Tried as below but getting an exception - “AEROSPIKE\_ERR\_FAIL\_FORBIDDEN” So, how to get the PK of existing (previously inserted) record? ```auto aql> set record_print_metadata TRUE RECORD_PRINT_METADATA = true aql> aql> set output table OUTPUT = TABLE aql> aql> select * from ovn_test.testset +---------+-----+--------------------------------+-----------+-------+-------+ | name | age | {edigest} | {set} | {ttl} | {gen} | +---------+-----+--------------------------------+-----------+-------+-------+ | "name2" | 38 | "rBD160onhKhHpbXrAWfOHmm1xsM=" | "testset" | -1 | 1 | | "name" | 36 | "v2wdE+fNEMW9Ai0n598XDAvM1uE=" | "testset" | -1 | 2 | +---------+-----+--------------------------------+-----------+-------+-------+ 2 rows in set (0.282 secs) OK aql> select * from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE=" +--------+-----+-------+-------+ | name | age | {ttl} | {gen} | +--------+-----+-------+-------+ | "name" | 36 | -1 | 2 | +--------+-----+-------+-------+ 1 row in set (0.001 secs) OK aql> delete from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE=" Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN aql> ``` ---
**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\ **Post date:** [December 24, 2020, 7:07pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/20 "2020-12-24T19:07:44Z")
``` Aerospike Query Client Version 3.29.0 C Client Version 4.6.17 Copyright 2012-2020 Aerospike. All rights reserved. aql> show namespaces +------------+ | namespaces | +------------+ | "ovn_test" | +------------+ [127.0.0.1:3000] 1 row in set (0.002 secs) OK aql> select * from ovn_test 0 rows in set (0.121 secs) OK aql> insert into ovn_test.testset (pk, bin1, bin2) values ("k1", 'abc', 123) OK, 1 record affected. aql> select * from ovn_test +-------+------+ | bin1 | bin2 | +-------+------+ | "abc" | 123 | +-------+------+ 1 row in set (0.118 secs) OK aql> set record_print_metadata true RECORD_PRINT_METADATA = true aql> select * from ovn_test +-------+------+--------------------------------+-----------+-------+-------+ | bin1 | bin2 | {edigest} | {set} | {ttl} | {gen} | +-------+------+--------------------------------+-----------+-------+-------+ | "abc" | 123 | "E9xE66jt4u+dwdx5jkQjvh5trO4=" | "testset" | -1 | 1 | +-------+------+--------------------------------+-----------+-------+-------+ 1 row in set (0.117 secs) OK aql> delete from ovn_test.testset where edigest="E9xE66jt4u+dwdx5jkQjvh5trO4=" OK, 1 record affected. aql> select * from ovn_test 0 rows in set (0.115 secs) OK aql> ``` ---
**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\ **Post date:** [December 24, 2020, 7:39pm UTC](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564/21 "2020-12-24T19:39:31Z")
I assume the user has write access? (If you are using Enterprise Edition, security is enabled.) See for details: [https://www.aerospike.com/docs/operations/configure/security/access-control/index.html#users](https://www.aerospike.com/docs/operations/configure/security/access-control/index.html#users) [Next page](https://discuss.aerospike.com/t/multiple-rows-delete-how-to-write-a-delete-query/1564.md?page=2)