DELETE

Description

DELETE statement removes rows from a single table or multiple tables.

Syntax

Single-Table Syntax

1. DELETE FROM tbl_name [[AS] tbl_alias]
2. [WHERE where_condition]
3. [ORDER BY ...]
4. [LIMIT row_count]

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

Explanations

• The conditions in the optional WHERE clause identify which rows to delete. With no WHERE clause, all rows are deleted.
• If the ORDER BY clause is specified, the rows are deleted in the order that is specified. -The LIMIT clause places a limit on the number of rows that can be deleted.

Examples

• Single-Table Examples

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

5. mysql> select * from t1;
6. +------+------+
7. | a    | b    |
8. +------+------+
9. |    1 |    2 |
10. +------+------+

• Multiple-Table Examples

Multiple-table join Syntax is also supported.

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

9. mysql> select * from t1;
10. +------+
11. | a    |
12. +------+
13. |    1 |
14. |    4 |
15. +------+
16. 2 rows in set (0.00 sec)

1. drop database if exists db1;
2. drop database if exists db2;
3. create database db1;
4. create database db2;
5. use db2;
6. drop table if exists t1;
7. create table t1 (a int);
8. insert into t1 values (1),(2),(4);
9. use db1;
10. drop table if exists t2;
11. create table t2 (b int);
12. insert into t2 values(1),(2),(3);
13. delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a where 2 > 1;

15. mysql> select * from db1.t2;
16. +------+
17. | b    |
18. +------+
19. |    3 |
20. +------+
21. mysql> select * from db2.t1;
22. +------+
23. | a    |
24. +------+
25. |    4 |
26. +------+
27. 1 row in set (0.00 sec)