SQL Mutations
Mutation - DELETE
Overview
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 theDROP 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 theWHERE
clause cannot contain sub-SELECT.
Syntax
The syntax for DELETE
mutation is as follows:
DELETE FROM table
WHERE conditions;
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.
Example
- Let’s create a sample table named
orders
that track customer orders.
CREATE TABLE orders (
order_id INT,
customer_name TEXT,
product_id INT,
quantity INT,
order_status TEXT
);
INSERT INTO orders (order_id, customer_name, product_id, quantity, order_status)
VALUES
(101, 'Alice Johnson', 1, 3, 'shipped'),
(102, 'Bob Smith', 2, 1, 'pending'),
(103, 'Charlie Brown', 3, 2, 'completed'),
(104, 'David White', 1, 1, 'pending'),
(105, 'Eva Davis', 4, 4, 'shipped');
- This creates a table named orders and inserts some sample data.
SELECT * FROM orders;
- You’ll get the following table:
order_id | customer_name | product_id | quantity | order_status
----------+---------------+------------+----------+--------------
101 | Alice Johnson | 1 | 3 | shipped
102 | Bob Smith | 2 | 1 | pending
103 | Charlie Brown | 3 | 2 | completed
104 | David White | 1 | 1 | pending
105 | Eva Davis | 4 | 4 | shipped
- Let’s say we want to delete orders with a quantity less than or equal to 2.
DELETE FROM orders
WHERE quantity <= 2;
- The output shows that the order with
order_id
:102
,103
, and104
are deleted because they have a quantity less than 2.
order_id | customer_name | product_id | quantity | order_status
----------+---------------+------------+----------+--------------
101 | Alice Johnson | 1 | 3 | shipped
105 | Eva Davis | 4 | 4 | shipped