You're viewing version 2.9 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation. For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy.
Delete
The DELETE
statement deletes documents that satisfy the predicates in the WHERE
clause. If you don’t specify the WHERE
clause, all documents are deleted.
Setting
The DELETE
statement is disabled by default. To enable the DELETE
functionality in SQL, you need to update the configuration by sending the following request:
PUT _plugins/_query/settings
{
"transient": {
"plugins.sql.delete.enabled": "true"
}
}
Syntax
Rule singleDeleteStatement
:
Example
SQL query:
DELETE FROM accounts
WHERE age > 30
Explain:
{
"size" : 1000,
"query" : {
"bool" : {
"must" : [
{
"range" : {
"age" : {
"from" : 30,
"to" : null,
"include_lower" : false,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
"_source" : false
}
Result set:
{
"schema" : [
{
"name" : "deleted_rows",
"type" : "long"
}
],
"total" : 1,
"datarows" : [
[
3
]
],
"size" : 1,
"status" : 200
}
The datarows
field shows the number of documents deleted.