Purpose
You can use this statement to delete rows that meet the conditions from one or more tables.
Direct DELETE operations on subqueries are not supported for either a single table or multiple tables. For example, DELETE FROM (SELECT * FROM t1); cannot be executed.
Syntax
Single-Table-Delete Syntax:
DELETE [hint_options] [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[PARTITION (partition_name,...)]
[WHERE where_condition]
[ORDER BY order_expression_list]
[LIMIT row_count]
Multiple-Table-Delete Syntax:
DELETE [hint_options] [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [hint_options] [LOW_PRIORITY] [QUICK] FROM table_name[.*] [, table_name[.*]] ...
USING table_references
[WHERE where_condition]
where_condition:
expression
order_expression_list:
order_expression [, order_expression ...]
order_expression:
expression [ASC | DESC]
limit_row_count:
INT_VALUE
table_references:
{table_name | joined_table | table_subquery | select_with_parents} [, ...]
Parameters
| Parameter | Description |
|---|---|
| hint_options | The hint options. |
| LOW_PRIORITY | Optional. Specifies to defer the execution of the DELETE statement until no other clients read data from the table. In the current version, only the keyword syntax is supported. The functionality does not actually take effect. |
| [UNIQUE] | Optional. Specifies to accelerate the DELETE operation. In the current version, only the keyword syntax is supported. The functionality does not actually take effect. |
| IGNORE | Optional. Specifies to ignore the ignorable errors during the execution of the DELETE operation. In the current version, only the keyword syntax is supported. The functionality does not actually take effect. |
| table_name | The name of the table from which rows are to be deleted. |
| partition_name | The name of a target partition in the table from which rows are to be deleted. |
| where_condition | The condition that the rows to be deleted must meet. |
| order_expression_list | The list of sort keys for the table from which rows are to be deleted. |
| row_count | The number of rows to be deleted. The specified value must be an integer. |
| table_references | The sequence of tables to be selected when you delete rows from two or more tables. |
Examples
Sample tables and their data are defined as follows:
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
4 rows in set
Single-table delete: Delete the row where
c1 = 2. Thec1column is the primary key column of thet1table.obclient> DELETE FROM t1 WHERE c1 = 2; Query OK, 1 row affected (0.02 sec) obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in setSingle-table delete: Delete the first row from the
t1table after its rows are sorted by thec2column.obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1; Query OK, 1 row affected obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in setSingle-table delete: Delete rows in the
p2partition from thet2table.obclient> DELETE FROM t2 PARTITION(p2); Query OK, 3 rows affected obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in setMulti-table delete: Delete rows where
t1.c1 = t2.c1from thet1andt2tables.obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1; Query OK, 3 rows affected obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in setMulti-table delete: Delete rows where
t1.c1 = t2.c1from thet1andt2tables.obclient> DELETE FROM t1, t2 USING t1,t2 WHERE t1.c1 = t2.c1; Query OK, 6 rows affected obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 4 | 4 | +----+------+ 1 row in set obclient> SELECT * FROM t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in setMulti-table delete: Delete rows where
t1.c1 = t2.c1from thep2partition in thet2table.obclient> DELETE t2 FROM t1,t2 PARTITION(p2) WHERE t1.c1 = t2.c1; Query OK, 3 rows affected obclient> select * from t2; +----+------+ | c1 | c2 | +----+------+ | 5 | 5 | +----+------+ 1 row in setDelete rows from an updatable view named
v.obclient> CREATE VIEW v AS SELECT * FROM t1; Query OK, 0 rows affected obclient> DELETE FROM v WHERE v.c1 = 1; Query OK, 1 row affected obclient> SELECT * FROM v; +----+------+ | c1 | c2 | +----+------+ | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 3 rows in set