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 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.

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

1. 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');

2. This creates a table named orders and inserts some sample data.

SELECT * FROM orders;

3. 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

4. Let’s say we want to delete orders with a quantity less than or equal to 2.

DELETE FROM orders
WHERE quantity <= 2;

5. The output shows that the order with order_id: 102, 103, and 104 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