Tutorials

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';