[SQL] Deleting

Last but not least, instead of updating something, you might just want to delete it altogether. This can be useful if entries are not needed anymore; for example, when a user closes her account on a website, or if you want to automatically remove obsolete data that’s more than 5 years old. Again, a distinction has to be made between multiple types of deletion: deleting a database, deleting a table, and deleting an entry.

Deleting Databases

To delete a complete database, use

I must strongly urge you to always make sure you don’t create a program that might accidentally remove something, or that might allow users to remove something. SQL doesn’t send you a warning message to confirm if you really want to delete the database – once you’ve issued the command, the database is gone.

Deleting Tables

To delete a table, use

Deleting Entries

To delete one or multiple entries, use

Again, do not forget to include the WHERE clause, or all entries within the table will be deleted. Always make sure you have the proper condition for deleting an entry. This mostly means that the condition should not return true in unexpected cases – make sure every entry has something that makes it unique, so you don’t accidentally remove more rows than you intended.

-- Juan's cover has been compromised, and his secret spy name leaked, so he needs to remove all traces of him on the interwebs
DELETE FROM accounts WHERE (id = 3 && secret_spy_name = "Juan");

Truncation

If you want to remove all entries, however, there’s a quicker and better way. Erasing all the data within a table, but not erasing the table itself, is called truncating. The syntax is

-- Oh boy, our enemies tortured Juan to find out the names of his comrads, and he gave in to the pressure! We need to remove all traces of all spies.
TRUNCATE TABLE accounts;