Delete data

Use the DELETE statement to delete data.

Example:

Create Tables t1 and t2 that contain the following data: Table t2 is a key-based partition table and the partition names are automatically generated by the system according to the partitioning command rules. That is, the partition names are p0, p1, p2, and p3.

obclient> CREATE TABLE t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t1; 
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)

obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition BY key(c1) partitions 4;
Query OK, 0 rows affected (0.19 sec)
obclient> INSERT INTO t2 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.02 sec)

• Delete data in a single table: Delete rows where c1 = 2 from Table t1. The values in Column c1 are primary keys.

  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 set (0.01 sec)
  

• Delete data in a single table: Delete the data in the first row sorted by Column c2from Table t1.

  obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1;
  Query OK, 1 row affected (0.01 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  2 |    2 |
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  3 rows in set (0.00 sec)
  

• Delete data in a single table: Delete data in Partition p2 from Table t2.

  obclient> SELECT * FROM t2 PARTITION(p2); 
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  |  3 |    3 |
  +----+------+
  3 rows in set (0.01 sec)
  
  obclient> DELETE FROM t2  PARTITION(p2); 
  Query OK, 3 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   | 
  +----+------+
  |  5 |    5 |
  +----+------+
  1 row in set (0.02 sec)
  

• Delete data in two or more tables: Delete rows where t1.c1 = t2.c1 from Tables t1 and t2.

  obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;
  Query OK, 3 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  4 |    4 |
  +----+------+
  1 row in set (0.01 sec)
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   |
  +----+------+ 
  |  5 |    5 | 
  +----+------+
  1 row in set (0.01 sec)
  

• Delete data in two or more tables:Delete rows where t1.c1 = t2.c1 from Tables t1 and t2.

  obclient> DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1;
  Query OK, 4 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  4 |    4 |
  +----+------+
  1 row in set (0.01 sec)
  
  obclient> SELECT * FROM t2;
  Empty set (0.01 sec)
  

For more information on the syntax of the DELETE statement, see the "DELETE" topic in SQL Reference (MySQL Mode).