MySQL SQL Syntax

Delete statement

Syntax:

DELETE [deleteoptions] FROM tablename [WHERE condition] [ORDER BY ordercolumn [DESC]] [LIMIT maxrecords ]

DELETE deletes the records in a table encompassed by condition.

. deleteoptions: The LOW_PRIORITY option has the effect that the data records are deleted only when all read operations are complete. (The goal of this option is to avoid having SELECT queries unnecessarily delayed due to DELETE operations.)

The option QUICK has the effect that during deletion, an existing index is not optimized. This speeds up the DELETE command, but it can lead to a somewhat inefficient index.

. condition: This condition specifies which records are to be deleted.

. ordercolumn: With ORDER BY you can first sort the data to be deleted. This makes sense only in combination with LIMIT, in order, for example, to delete the first or last ten records (according to some sort criterion).

. maxrecords: With LIMIT, the maximum number of records that may be deleted is specified.

If DELETE is executed without conditions, then all records of the table are deleted (so be careful!). DELETE without conditions cannot be part of a transaction. If a transaction is open, it is closed with COMMIT before the DELETE command is executed. If you wish to delete large tables completely, it is more efficient to use the command TRUNCATE.

DELETE [deleteoptions] table1, table2, ... FROM table1, table2, table3, ... [USING columns] WHERE conditions

This variant of DELETE (available since version 4.0) deletes records from tables table1, table2, etc., where the data of additional tables (table3, etc.) are considered in the search criteria.

After DELETE, all tables from which data are to be deleted must be specified. After FROM, all DELETE tables must appear, as well as any additional tables that serve only in formulating the search criteria.

. deleteoptions: Here you can specify options as in a usual DELETE command. . columns: Here fields that link the tables can be specified. This assumes that the linking field has the same name in both tables. . conditions: In addition to the usual delete criteria, here one may specify linking conditions (e.g., WHERE table1.id = table2.forgeinID).