The DELETE mutation deletes one or more records from a table based on specified conditions. This support has limitations:
- Only one data mutation (DELETE or UPDATE) at a given moment is possible, trying to run another one will fail.
- Data mutations rewrite all files containing the data from the UPDATE/DELETE condition. Running DELETE from the table without any condition is possible, but it will be much slower than the DROP TABLE table.
- The syntax is simplified in comparison to Postgres. For example, the SET column=<value> operation doesn't support sub-SELECT as the value, and the WHERE clause cannot contain sub-SELECT.
Feature Update: Initial Release
This marks the initial release of our new Mutation feature, which allows you to update data. The Mutations involve rewriting entire files, which takes several minutes and could generate infrastructure costs.
We are aiming to enhance and optimize this feature in future updates!
The syntax for DELETE mutation is as follows:
In this syntax:
- table: The table name from which you want to delete records.
- WHERE conditions (Optional): The conditions must be met for the deletion to execute. If no conditions are provided, all records from the table will be deleted.
1) Let's create a sample table named orders that track customer orders.
2) This creates a table named orders and inserts some sample data.
You'll get the following table:
3) Let's say we want to delete orders with a quantity less than or equal to 2.
4) The output shows that the order with order_id: 102, 103, and 104 are deleted because they have a quantity <= 2.