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] FROM table_name
[PARTITION (partition_name,...)]
[WHERE where_condition]
[ORDER BY order_expression_list]
[LIMIT row_count]
Multiple-Table-Delete Syntax:
DELETE [hint_options] table_name[.*] [, table_name[.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [hint_options] 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 | Specifies the hint options. |
| 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 tablest1andt2.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 tablest1andt2.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