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
DROP DATABASE someDatabaseName;
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
DROP TABLE someTableName;
Deleting Entries
To delete one or multiple entries, use
DELETE FROM someTableName WHERE condition;
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");
id | secret_spy_name | password |
1 | Panda | whatislovebabydonthurtme |
2 | Ginty | pikapika |
4 | GandalfTheGr | flyyoufools |
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
TRUNCATE TABLE someTableName;
-- 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;
id | secret_spy_name | password |
No entries. |