Delete Data

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

Prerequisites

Complete the Deploy standalone MatrixOne.

SQL Statements for Deleting Data

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

Here are the differences between them:

  • DELETE FROM: Use DELETE FROM to delete specific records.
  • TRUNCATE TABLE: Use TRUNCATE TABLE when you want to keep the table structure, indexes, and constraints intact, but delete all records.
  • DROP TABLE: Use DROP TABLE when you no longer need the table.

DELETE

  1. DELETE FROM tbl_name [[AS] tbl_alias]
  2.     [WHERE where_condition]
  3.     [ORDER BY ...]
  4.     [LIMIT row_count]

  1. DELETE FROM tbl_name: Specifies the target table from which data will be deleted. tbl_name is the table name.

  2. [AS] tbl_alias (optional): You can use the AS keyword to assign a table alias (tbl_alias) to the target table. The alias is optional and used to simplify the query and reference the table in the statement.

  3. [WHERE where_condition] (optional): The WHERE clause specifies the conditions for deleting data. Only rows that satisfy the specified conditions will be deleted. where_condition is a logical expression that can use various comparisons and logical operators to define the conditions.

  4. [ORDER BY ...] (optional): The ORDER BY clause is used to sort the rows to be deleted based on specified columns. You can use one or more columns and specify ascending (ASC) or descending (DESC) order. The sorting affects the order of the deleted rows.

  5. [LIMIT row_count] (optional): The LIMIT clause limits the number of rows deleted from the table. It specifies the maximum number of rows (row_count) to be deleted. If the LIMIT clause is not specified, all rows satisfying the WHERE condition will be deleted.

TRUNCATE

  1. TRUNCATE [TABLE] table_name;

The TRUNCATE statement deletes all data in a table while preserving the table structure. It quickly clears the table without deleting rows one by one.

  • [TABLE] (optional) is a keyword that provides more explicit syntax but can be omitted in most database systems.
  • table_name is the name of the target table.

DROP

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

The DROP TABLE statement completely removes a table, including its structure and data, from the database.

  • [IF EXISTS] (optional) is a keyword that performs the deletion only if the table exists. An error will occur if omitted, and the table to be dropped does not exist.
  • [db.] (optional) specifies the database name where the table resides. If no database name is provided, the current database is assumed.
  • name is the name of the table to be dropped.

Garbage Collection

By default, MatrixOne does not immediately delete data from the disk after running DELETE, DROP, or TRUNCATE statements. Instead, it marks the data as deletable. Then, the GC (Garbage Collection) mechanism periodically scans and cleans up the no longer-needed old data.

By default, the garbage collection mechanism scans every 30 minutes. During each scan, it identifies data deleted through SQL statements for over 1 hour and starts the cleanup process to release disk space. The most extended cycle to complete all deletions is 90 minutes. Therefore, it is essential to note that executing DELETE, DROP, or TRUNCATE statements do not immediately reduce disk usage. Only data marked as deletable during the garbage collection will be cleaned up, and disk space will be freed.

Examples

  • Example 1
  1. -- Create table
  2. CREATE TABLE employees (
  3.   id INT PRIMARY KEY,
  4.   name VARCHAR(50),
  5.   department VARCHAR(50)
  6. );
  8. -- Insert data
  9. INSERT INTO employees (id, name, department)
  10. VALUES (1, 'John Doe', 'HR'),
  11.        (2, 'Jane Smith', 'Marketing'),
  12.        (3, 'Mike Johnson', 'IT'),
  13.        (4, 'Emily Brown', 'Finance');
  15. -- View initial data
  16. mysql> SELECT * FROM employees;
  17. +------+--------------+------------+
  18. | id   | name         | department |
  19. +------+--------------+------------+
  20. |    1 | John Doe     | HR         |
  21. |    2 | Jane Smith   | Marketing  |
  22. |    3 | Mike Johnson | IT         |
  23. |    4 | Emily Brown  | Finance    |
  24. +------+--------------+------------+
  25. 4 rows in set (0.01 sec)
  27. -- Delete partial data
  28. mysql> DELETE FROM employees WHERE department = 'IT';
  29. Query OK, 1 row affected (0.01 sec)
  31. -- View data after the deletion
  32. mysql> SELECT * FROM employees;
  33. +------+-------------+------------+
  34. | id   | name        | department |
  35. +------+-------------+------------+
  36. |    1 | John Doe    | HR         |
  37. |    2 | Jane Smith  | Marketing  |
  38. |    4 | Emily Brown | Finance    |
  39. +------+-------------+------------+
  40. 3 rows in set (0.00 sec)
  • Example 2
  1. -- Create table
  2. CREATE TABLE orders (
  3.   order_id INT PRIMARY KEY,
  4.   customer_name VARCHAR(50),
  5.   order_date DATE
  6. );
  8. -- Insert data
  9. INSERT INTO orders (order_id, customer_name, order_date)
  10. VALUES (1, 'John Doe', '2022-01-01'),
  11.        (2, 'Jane Smith', '2022-02-01'),
  12.        (3, 'Mike Johnson', '2022-03-01'),
  13.        (4, 'Emily Brown', '2022-04-01'),
  14.        (5, 'David Wilson', '2022-05-01');
  16. -- View initial data
  17. mysql> SELECT * FROM orders;
  18. +----------+---------------+------------+
  19. | order_id | customer_name | order_date |
  20. +----------+---------------+------------+
  21. |        1 | John Doe      | 2022-01-01 |
  22. |        2 | Jane Smith    | 2022-02-01 |
  23. |        3 | Mike Johnson  | 2022-03-01 |
  24. |        4 | Emily Brown   | 2022-04-01 |
  25. |        5 | David Wilson  | 2022-05-01 |
  26. +----------+---------------+------------+
  27. 5 rows in set (0.01 sec)
  29. -- Delete the earliest two orders
  30. mysql> DELETE FROM orders
  31.        WHERE order_id IN (
  32.        SELECT order_id
  33.        FROM orders
  34.        ORDER BY order_date
  35.        LIMIT 2);
  36. Query OK, 2 rows affected (0.01 sec)
  38. -- View data after the deletion
  39. mysql> SELECT * FROM orders;
  40. +----------+---------------+------------+
  41. | order_id | customer_name | order_date |
  42. +----------+---------------+------------+
  43. |        3 | Mike Johnson  | 2022-03-01 |
  44. |        4 | Emily Brown   | 2022-04-01 |
  45. |        5 | David Wilson  | 2022-05-01 |
  46. +----------+---------------+------------+
  47. 3 rows in set (0.01 sec)