DELETE

Description

You can delete records in the table by using the DELETE statement. You can specify delete conditions by combining the statement with the WHERE Clause. You can delete multiple tables. You can delete one or more tables with one DELETE statement.

If you want to delete one table, the LIMIT Clause can be specified. You can limit the number of records by specifying the LIMIT Clause.  If the number of records satisfying the WHERE Clause exceeds row_count, only the number of records specified in row_count will be deleted.

Syntax

<DELETE single table>

DELETE [FROM] table_name [ WHERE <search_condition> ] [LIMIT row_count]

 

<DELETE multiple tables FROM ...>

DELETE table_name[, table_name] ... FROM <table_specifications> [ WHERE <search_condition> ]

 

<DELETE FROM multiple tables USING ...>

DELETE FROM table_name[, table_name] ... USING <table_specifications> [ WHERE <search_condition> ]

• <table_specifications>: You can specify the statement such as FROM clause of the SELECT  statement and one or more tables can be specified.
• table_name: Specifies the name of a table where the data to be deleted is contained. If the number of table is one, the FROM keyword can be omitted.
• search_condition: Deletes only data that meets search_condition by using WHERE Clause. If it is specified, all data in the specified tables will be deleted.
• row_count: Specifies the number of records to be deleted in the LIMIT Clause. An integer greater than 0 can be specified.

Remark

• On the DELETE statement which has multiple tables, the table alias can be defined within <table_specifications> only. At the outside of <table_specifications>, the table alias defined in <table_specifications> can be used (see Example 2).
• In 2008 R4.x or lower versions, only one table can be entered in <table_specifications>.

Example 1

CREATE TABLE a_tbl(

id INT NOT NULL,

phone VARCHAR(10));

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);

 

DELETE FROM a_tbl WHERE phone IS NULL LIMIT 1;

 

--delete one record only from a_tbl

SELECT * FROM a_tbl;

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

            5  NULL

 

--delete all records from a_tbl

DELETE FROM a_tbl;

Example 2

CREATE TABLE a_tbl(

id INT NOT NULL,

phone VARCHAR(10));

 

CREATE TABLE b_tbl(

id INT NOT NULL,

phone VARCHAR(10));

 

CREATE TABLE c_tbl(

id INT NOT NULL,

phone VARCHAR(10));

 

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);

 

INSERT INTO b_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL);

 

INSERT INTO c_tbl VALUES(1,'111-1111'), (2,'222-2222'), (10, '333-3333'), (11, NULL), (12, NULL);

 

-- Below four queries show the same result.

--  <DELETE multiple tables FROM ...>

 

DELETE a, b FROM a_tbl a, b_tbl b

WHERE a.id=b.id;

 

DELETE a, b FROM a_tbl a INNER JOIN b_tbl b ON a.id=b.id

INNER JOIN c_tbl c ON b.id=c.id;

 

-- <DELETE FROM multiple tables USING ...>

 

DELETE FROM a, b USING a_tbl a, b_tbl b, c_tbl c

WHERE a.id=b.id AND b.id=c.id;

 

DELETE FROM a, b USING a_tbl a INNER JOIN b_tbl b ON a.id=b.id

INNER JOIN c_tbl c ON b.id=c.id;