All Products
Search
Document Center

DELETE

Last Updated: Jun 18, 2021

Description

You can execute the DELETE statement to delete rows that can satisfy specified conditions from one or more tables.

Syntax

Single-Table-Delete Syntax:
    DELETE [hint_options] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY order_expression_list]
    [LIMIT row_count]

Multiple-Table-Delete Syntax:
    DELETE [hint_options] tbl_name[.*] [, tbl_name[. *]] ...
    FROM table_references
    [WHERE where_condition]
Or:
    DELETE [hint_options] FROM tbl_name[.*] [, tbl_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:
    {tbl_name | joined_table | table_subquery | select_with_parens} [, ...]
 

Parameters

Parameter

Description

hint_options

The hint.

tbl_name

The name of the table that you want to delete.

partition_name

The name of the table partition from which you want to delete data.

where_condition

The filter conditions. The system deletes the table rows that can satisfy the specified conditions.

order_expression_list

Sort the column values of the table from which you want to delete data. The values are sorted based on the sort keys.

row_count

The number of table rows that you want to delete. The value must be an integer.

table_references

The tables from which you want to delete data.

Examples

The following examples are based on tables t1 and t2.

OceanBase(admin@test)>create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)

OceanBase(admin@test)>create table t2(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected (0.19 sec)
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.02 sec)
  • Delete a row from a table: Delete a row where the value for the c1 column is equal to 2. The c1 column is the primary key of the t1 table.
OceanBase(admin@test)>DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  4 |    4 |
+----+------+
3 rows in set (0.01 sec)
  • Delete a row from a table: Sort the rows in the t2 table by the c2 column, and delete the first row.
OceanBase(admin@test)>DELETE FROM t1 ORDER BY c2 LIMIT 1;
Query OK, 1 row affected (0.01 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
3 rows in set (0.00 sec)
  • Delete a row from a table: Delete the p2 partition data from the t2 table.
OceanBase(admin@test)>DELETE FROM t2 PARTITION(p2);
Query OK, 3 rows affected (0.02 sec)

OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+
1 row in set (0.02 sec)
  • Delete rows from multiple tables: In the t1 and t2 tables, find the rows where the value for the c1 column in t1 is equal to that in t2, and delete the rows from t1 and t2.
OceanBase(admin@test)>DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;
Query OK, 3 rows affected (0.02 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    4 |
+----+------+
1 row in set (0.01 sec)

OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+
1 row in set (0.01 sec)
  • Delete rows from multiple tables: In the t1 and t2 tables, find the rows where the value for the c1 column in t1 is equal to that in t2, and delete the rows from t1 and t2.
OceanBase(admin@test)>DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1;
Query OK, 4 rows affected (0.02 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  4 |    4 |
+----+------+
1 row in set (0.01 sec)

OceanBase(admin@test)>select * from t2;
Empty set (0.01 sec)
  • Delete rows from multiple tables: In the t1 table and the p2 partition of the t2 table, find the rows where the value for the c1 column in t1 is equal to that in t2, and delete the rows from t1 and t2.
OceanBase(admin@test)>DELETE t2 FROM t1,t2 PARTITION(p2) WHERE t1.c1 = t2.c1;
Query OK, 3 rows affected (0.02 sec)

OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+
1 row in set (0.01 sec)
  • Delete a row from updatable view v.
OceanBase(admin@test)>create view v as select * from t1;
Query OK, 0 rows affected (0.07 sec)

OceanBase(admin@test)>delete from v where v.c1 = 1;
Query OK, 1 row affected (0.02 sec)

OceanBase(admin@test)>select * from v;
+----+------+
| c1 | c2   |
+----+------+
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
3 rows in set (0.01 sec)

Notes

You cannot use subqueries to delete rows from one or more tables. For example, you cannot execute the following statement:

delete from (select * from t1);