Delete data

This document describes how to use SQL statements to delete data in MatrixOne.

Delete data SQL statement

You can delete data in three ways: DROP TABLE, TRUNCATE TABLE and DELETE TABLE.

The difference between the three is:

DELETE TABLE: When you want to delete a partial record, use DELETE TABLE.
TRUNCATE TABLE: When you still want to keep the table, the structure, index, and constraints of the table need to be kept unchanged, but to delete all records, use TRUNCATE TABLE.
DROP TABLE: Use DROP TABLE when you no longer need the table.

`DELETE`

DELETE FROM tbl_name [[AS] tbl_alias]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE FROM tbl_name: Specifies the target table to delete data from the table. tbl_name is the name of the table.
[AS] tbl_alias (optional): You can use the AS keyword to specify a table alias (tbl_alias) to the target table. Alias are optional and are used to simplify queries and reference tables in statements.
[WHERE where_condition] (optional): The WHERE clause is used to specify the conditions for deleting data. Only rows that meet the specified criteria will be deleted. where_condition is a logical expression that can use various comparison operators and logical operators to define conditions.
[ORDER BY ...] (optional): The ORDER BY clause is used to sort the rows to be deleted by the specified column. One or more columns can be used and can specify ascending (ASC) or descending (DESC) sorting. Sort will affect the order of deleted rows.
[LIMIT row_count] (optional): The LIMIT clause is used to limit the number of rows deleted from the table. It specifies the maximum number of rows to be deleted (row_count). If the LIMIT clause is not specified, all rows that meet the WHERE condition are deleted.

`TRUNCATE`

> TRUNCATE [TABLE] table_name;

The TRUNCATE statement is used to delete all data in the table and preserve the table's structure. It will quickly clear the table instead of deleting the data row by row.

The [TABLE]` (optional) keyword is used to provide clearer syntax, but can be omitted in most database systems.
table_name is the name of the target table to be operated on.

`DROP`

> DROP TABLE [IF EXISTS] [db.]name

The DROP TABLE statement is used to completely delete tables in the database, including the structure and data of the table.

The [IF EXISTS]` (optional) keyword indicates that if the table exists, the delete operation is performed. If this keyword is omitted and the table to be deleted does not exist, an error will be raised.
[db.] (optional) Specifies the database name where the table resides. If the database name is not provided, the default is the current database.
name is the name of the table to be deleted.

GC (Garbage Collection) mechanism

In default configuration, MatrixOne does not delete data from disk immediately after executing the DELETE, DROP, or TRUNCATE statement, but marks the data as deletionable. The GC (garbage collection) mechanism then scans regularly and cleans up old data that is no longer needed.

By default, the garbage collection mechanism scans every 30 minutes. Each scan looks for data that has been deleted through SQL statements for more than 1 hour and starts a cleanup operation to free up disk space. The maximum cycle for completing all cleanings is 90 minutes. Therefore, it is important to note that executing the DELETE, DROP, or TRUNCATE statements does not immediately reduce disk usage. Only during garbage collection, data marked as deletion can be cleaned and freed up space.

Example

Example 1

-- Create a table
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

-- Insert data
INSERT INTO employees (id, name, department)
VALUES (1, 'John Doe', 'HR'),
       (2, 'Jane Smith', 'Marketing'),
       (3, 'Mike Johnson', 'IT'),
       (4, 'Emily Brown', 'Finance');

-- View the initial data
mysql> SELECT * FROM employees;
+-------+-------------------------------+
| id | name | department |
+-------+-------------------------------+
| 1 | John Doe | HR |
| 2 | Jane Smith | Marketing |
| 3 | Mike Johnson | IT |
| 4 | Emily Brown | Finance |
+-------+-------------------------------+
4 rows in set (0.01 sec)

-- Delete some data
mysql> DELETE FROM employees WHERE department = 'IT';
Query OK, 1 row affected (0.01 sec)

-- View deleted data
mysql> SELECT * FROM employees;
+-------+--------------------------------+
| id | name | department |
+-------+--------------------------------+
| 1 | John Doe | HR |
| 2 | Jane Smith | Marketing |
| 4 | Emily Brown | Finance |
+-------+--------------------------------+
3 rows in set (0.00 sec)

Example 2

-- Create a table
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  order_date DATE
);

-- Insert data
INSERT INTO orders (order_id, customer_name, order_date)
VALUES (1, 'John Doe', '2022-01-01'),
       (2, 'Jane Smith', '2022-02-01'),
       (3, 'Mike Johnson', '2022-03-01'),
       (4, 'Emily Brown', '2022-04-01'),
       (5, 'David Wilson', '2022-05-01');

-- View the initial data
mysql> SELECT * FROM orders;
+----------------------------------------------------+
| order_id | customer_name | order_date |
+----------------------------------------------------+
| 1 | John Doe | 2022-01-01 |
| 2 | Jane Smith | 2022-02-01 |
| 3 | Mike Johnson | 2022-03-01 |
| 4 | Emily Brown | 2022-04-01 |
| 5 | David Wilson | 2022-05-01 |
+----------------------------------------------------+
5 rows in set (0.01 sec)

-- Delete the first two orders
mysql> DELETE FROM orders
       WHERE order_id IN (
       SELECT order_id
       FROM orders
       ORDER BY order_date
       LIMIT 2);
Query OK, 2 rows affected (0.01 sec)

-- View deleted data
mysql> SELECT * FROM orders;
+----------------------------------------------------+
| order_id | customer_name | order_date |
+----------------------------------------------------+
| 3 | Mike Johnson | 2022-03-01 |
| 4 | Emily Brown | 2022-04-01 |
| 5 | David Wilson | 2022-05-01 |
+----------------------------------------------------+
3 rows in set (0.01 sec)