DELETE

Grammar Description

DELETE is used to delete records in single or multiple tables.

Grammar Structure

Single table syntax structure

DELETE FROM tbl_name [[AS] tbl_alias]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

The DELETE statement deletes rows from tbl_name and returns the number of deleted rows.

Parameter definition

The WHERE clause is used to specify the conditions used to identify which rows to delete. If there is no WHERE clause, all lines are deleted.
ORDER BY clause, which refers to deleting lines in the specified order.
The LIMIT clause is used to limit the number of rows that can be deleted.

Example

-Single Example

CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
insert INTO t1 VALUES (1,1),(1,2);
delete from t1 where a=1 limit 1;

mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+

-Multiple Example:

It also supports multi-table JOIN statements.

drop table if exists t1;
drop table if exists t2;
create table t1 (a int);
insert into t1 values(1), (2), (4);
create table t2 (b int);
insert into t2 values(1), (2), (5);
delete t1 from t1 join t2 where t1.a = 2;

mysql> select * from t1;
+------+
| a |
+------+
| 1 |
| 4 |
+------+
2 rows in set (0.00 sec)

drop database if exists db1;
drop database if exists db2;
create database db1;
create database db2;
use db2;
drop table if exists t1;
create table t1 (a int);
insert into t1 values (1),(2),(4);
use db1;
drop table if exists t2;
create table t2 (b int);
insert into t2 values(1),(2),(3);
delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a where 2 > 1;

mysql> select * from db1.t2;
+------+
| b |
+------+
| 3 |
+------+
mysql> select * from db2.t1;
+------+
| a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)