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 . If you want to limit the number of records to be deleted, you can do so by specifying the number of records to be deleted after the LIMIT Clause. In this case, only row_count records are deleted even when the number of records satisfying the WHERE Clause exceeds row_count.

Syntax

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

 

<table_specification> ::= <table_hierarchy> | ( <table_hierarchy_comma_list )

 

<table_hierarchy> ::= [ ONLY ] <table_name> |

                      ALL <table_name> [ EXCEPT <table_specification> ]

Example

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;