DELETE
Word of warning: When using DELETE remember there is no "Trash" folder from which you can retrieve accidentally deleted records. Unless you have a backup, when it's gone it's gone.
As an alternative to deleting records you may want to consider adding a "deleted" column to the table and set this flag instead of physically removing the record. This is particularly useful for master file data where you have historical transaction data and need to preserve references.
Deleting all records
DELETE FROM tablename;
The above query will delete ALL the records from the table "tablename". If the table is large then it can be a slow process as it will update table indexes as it deletes. A more efficient way to completely clear all the records is
TRUNCATE tablename;
WHERE clause
Normally, you will use DELETE with a WHERE clause to define the record/s to be deleted
For example, to delete a single record using its primary key
DELETE FROM pupil WHERE pupilid = 27
To delete a group of records
DELETE FROM pupil WHERE classid = 'A'
ORDER BY clause
Use when you want to define the order in the records should be deleted. Again, often useful in conjunction with a LIMIT clause
Delete the record with the lowest score for a member
DELETE FROM score WHERE memberid = 101 ORDER BY score LIMIT 1
DELETE from multiple tables
You can delete records from multiple tables with a single delete query. The syntax for a multi-table delete is slightly different. You must specify the table/s you want to delete from and the tables used in the query
Syntax example
DELETE table1, table2 FROM table1 INNER JOIN table2 ON ... WHERE ...
Not unexpectedly, the cheque from Mr Beetsem-Daley, for the purchase of the academy, bounced due to an administrative oversight (forgetting to make sure he could afford it). The deal therefore fell through.
Remove the ex-academy pupils from the pupil table
DELETE pupil
FROM pupil
INNER JOIN house USING (houseid)
WHERE house_name = 'Academy';
Remove the ex-academy pupils from the pupil table and also delete the Academy house record
DELETE house, pupil
FROM pupil
INNER JOIN house USING (houseid)
WHERE house_name = 'Academy';