Delete Data with a Query
- Capella Operational
- how-to
How to delete documents using SQL++.
Introduction
To delete documents from a keyspace, you can use the DELETE statement or the MERGE statement.
If you want to try out the examples in this section, follow the instructions given in Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:
| Please note that the examples in this guide will alter the data in your sample database. To restore your sample data, remove and reinstall the travel sample data. Refer to Import Data with the Capella UI for details. |
Deleting Documents by Key
To delete one or more documents by key, use the DELETE statement with the USE KEYS hint:
-
Use the FROM keyword to specify the keyspace which contains the documents to be deleted.
-
Use the USE KEYS hint to specify a document keys or array of document keys.
-
If required, use the RETURNING clause to specify what should be returned when the documents are deleted.
You can combine this approach with the WHERE clause if necessary.
The following query deletes a document with the key "airline_4444" and returns the deleted document.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
DELETE FROM airline k
USE KEYS "airline_4444"
RETURNING k[
{
"k": {
"callsign": "MY-AIR",
"country": "United States",
"iata": "Z1",
"icao": "AQZ",
"name": "80-My Air",
"id": "4444",
"type": "airline"
}
}
]For more information and examples, refer to DELETE.
Deleting Documents by Filter
To delete documents by filter, use the DELETE statement with the WHERE clause:
-
Use the FROM keyword to specify the keyspace which contains the documents to be deleted.
-
Use the WHERE clause to specify the condition that needs to be met for documents to be deleted.
-
If required, use the LIMIT clause to specify the greatest number of documents that may be deleted.
-
If required, use the RETURNING clause to specify what should be returned when the documents are deleted.
You can combine this approach with the USE KEYS hint if necessary.
The following query deletes any airline whose callsign is "AIR-X", returning the content of the airline’s id field.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
DELETE FROM airline f
WHERE f.callsign = "AIR-X"
RETURNING f.id[
{
"id": "4445"
}
]Deleting Documents by Subquery
To delete documents based on the results returned by a SELECT query, use a subquery in the filter.
The following query finds the last city in alphanumeric order in the airport keyspace, and deletes any airports in that city.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
DELETE FROM airport
WHERE city IN (SELECT RAW MAX(t.city) FROM airport AS t)
RETURNING airportname;[
{
"airportname": "Île d'Yeu Airport"
}
]Merging and Deleting Documents
You can also delete documents by merging: that is, by joining one keyspace to another, and deleting any documents that match.
To delete documents using a merge, use the MERGE statement with the DELETE action:
-
Use the INTO keyword to specify the target. This is the data source containing the documents to delete.
-
Use the USING keyword to specify the source. This is the data source to check against the target.
-
Use the ON keyword to specify the merge predicate. This is a condition that must be met to match an object in the source with an object in the target.
-
Use WHEN MATCHED THEN DELETE to specify that when a document in the source matches a document in the target, the document in the target should be deleted.
-
If necessary, use the WHERE clause to specify any further conditions that must be met for documents to be deleted.
-
If required, use the LIMIT clause to specify the greatest number of documents that may be deleted.
-
If required, use the RETURNING clause to specify what should be returned when the documents are deleted.
The following query finds all BA routes whose source airport is in France. If any flights are using equipment 319, they are updated to use 797. If any flights are using equipment 757, they are deleted.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
MERGE INTO route
USING airport
ON route.sourceairport = airport.faa
WHEN MATCHED THEN UPDATE
SET route.old_equipment = route.equipment,
route.equipment = "797",
route.updated = true
WHERE airport.country = "France"
AND route.airline = "BA"
AND CONTAINS(route.equipment, "319")
WHEN MATCHED THEN DELETE
WHERE airport.country = "France"
AND route.airline = "BA"
AND CONTAINS(route.equipment, "757")
RETURNING route.old_equipment, route.equipment, airport.faa;[
{
"equipment": "763 757",
"sourceairport": "CDG"
},
{
"equipment": "797",
"old_equipment": "734 319",
"sourceairport": "BOD"
},
{
"equipment": "797",
"old_equipment": "319 320 321",
"sourceairport": "CDG"
},
{
"equipment": "797",
"old_equipment": "319",
"sourceairport": "LYS"
},
{
"equipment": "797",
"old_equipment": "319 320",
"sourceairport": "MRS"
},
{
"equipment": "797",
"old_equipment": "319 320 734",
"sourceairport": "NCE"
},
{
"equipment": "797",
"old_equipment": "319 320 321",
"sourceairport": "NCE"
},
{
"equipment": "797",
"old_equipment": "319 320",
"sourceairport": "ORY"
},
{
"equipment": "797",
"old_equipment": "320 319 321",
"sourceairport": "TLS"
}
]For more information and examples, refer to MERGE.