This version of the OpenSearch documentation 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:

  1. PUT _plugins/_query/settings
  2. {
  3.   "transient": {
  4.     "plugins.sql.delete.enabled": "true"
  5.   }
  6. }

Syntax

Rule singleDeleteStatement:

singleDeleteStatement

Example

SQL query:

  1. DELETE FROM accounts
  2. WHERE age > 30

Explain:

  1. {
  2.   "size" : 1000,
  3.   "query" : {
  4.     "bool" : {
  5.       "must" : [
  6.         {
  7.           "range" : {
  8.             "age" : {
  9.               "from" : 30,
  10.               "to" : null,
  11.               "include_lower" : false,
  12.               "include_upper" : true,
  13.               "boost" : 1.0
  14.             }
  15.           }
  16.         }
  17.       ],
  18.       "adjust_pure_negative" : true,
  19.       "boost" : 1.0
  20.     }
  21.   },
  22.   "_source" : false
  23. }

Result set:

  1. {
  2.   "schema" : [
  3.     {
  4.       "name" : "deleted_rows",
  5.       "type" : "long"
  6.     }
  7.   ],
  8.   "total" : 1,
  9.   "datarows" : [
  10.     [
  11.       3
  12.     ]
  13.   ],
  14.   "size" : 1,
  15.   "status" : 200
  16. }

The datarows field shows the number of documents deleted.