(click anywhere to close)
OPEN MENU

[SQL] Deleting

category: Website | course: SQL | difficulty:

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");
idsecret_spy_namepassword
1Pandawhatislovebabydonthurtme
2Gintypikapika
4GandalfTheGrflyyoufools

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;
idsecret_spy_namepassword
No entries.
CONTINUE WITH THIS COURSE
Do you like my tutorials?
To keep this site running, donate some motivational food!
Crisps
(€2.00)
Chocolate Milk
(€3.50)
Pizza
(€5.00)