Course Module 4. Working with databases - Lecture: Deleting data in a table

5.1 DELETE FROM statement

What's easy to do in SQL is delete data. You can delete absolutely everything very quickly, and no one will even ask you for any confirmation.

Let's start with the simplest scenario: how to delete one row in a table .

This is the scenario you will see most often, it is usually the deletion of a specific record, and the standard query usually looks like:

DELETE FROM table
WHERE id = 133;

This is the only query where you do not need to specify the names of the columns: after all, the data is deleted immediately in rows.

The second scenario is deleting the rows that are specified by the id list , everything is also quite simple here:

DELETE FROM table
WHERE id IN (1, 2, 3, …);

The third scenario is to remove rows that match a certain condition:

DELETE FROM table
WHERE condition;

Let's say we want to fire all our programmers, then we need to write a request like:

DELETE FROM employee
WHERE occupation = 'Programmer';

And finally, if you want to delete all records, you can write a query like this:

DELETE FROM table

This simple query is enough to remove all records from a table. By the way, there will be no Ctrl + Z in this case. Recordings are simply deleted without the possibility of recovery and that's it. So make backups, more often .

5.2 Removing everything

For quick removal (to add headaches to users), SQL has a few more commands.

How to quickly delete all data in a table? Use the operator TRUNCATE :

TRUNCATE TABLE table

One typo in the name of the table - and a couple of days of data recovery are provided to you. Be glad you're not a database admin.

If you need to delete not just the data in the table, but the table itself, then there is an operator DROP for this :

DROP TABLE table

By the way, there are similar options with database schemas . If you want to delete the database itself, then:

DROP SCHEME database

or:

DROP DATABASE database

You can also use DROP to delete:

EVENT
FUNCTION
PROCEDURE
INDEX
VIEW
TRIGGER

And here are a couple of interesting stories related to data deletion:

Break of the day. GitLab deleted 300 GB of customer data due to sysadmin error

sudo rm -rf, or Chronicle of the GitLab.com database incident from 2017/01/31

Silicon Valley "Silicon Valley" - Data Deletion