The DELETE Statement

To remove records(data) from tables, use the DELETE command. The DELETE statement allows a WHERE clause that identifies which records to remove. To empty a table entirely by deleting all its records, use the following syntax:

DELETE FROM table_name;

To remove only specific records in a table, a DELETE statement needs to be issued with a WHERE clause that identifies which records to remove:

DELETE FROM table_name WHERE …;

Note: There is no need to mention any columns since it will delete whole rows at a time.

Using DELETE with ORDER BY and LIMIT DELETE supports ORDER BY and LIMIT clauses, which provide finer control over the way records are deleted. For example, LIMIT can be useful to remove only some instances of a given set of records.

Suppose that the people table contains five records where the name column equals 'Emily'. To keep only one such record, use the following statement to remove four of the duplicated records.

DELETE FROM people WHERE name='Emily' LIMIT 4;

Normally, MySQL makes no guarantees about which four of the five records selected by the WHERE clause it will delete. An ORDER BY clause in conjunction with LIMIT provides better control. For example, to delete four of the records containing 'Emily' but leave the one with the lowest id value, use ORDER BY and LIMIT together as follows;

DELETE FROM people WHERE name='Emily' ORDER BY id DESC LIMIT 4;

Note: The DELETE result will indicate number of rows affected, which can be zero (0) if the statement did not cause a change to be made.