Practical introduction to relational databases

Deleting one row

The delete syntax looks like this:

In order to delete exactly one row, the WHERE clause, or condition needs to match exactly one row in the table you're deleting from. Usually that set of conditions will include at least one indexed column, and most ideally it should be a column that has a non null unique index on it. We'll talk about the importance of this later.

For example if you have the following table, and you delete entry where id = 43, that row, and only that row will be gone.

Other than that when running a DELETE query the row you specified will be gone.

A curious side effect: if one of the columns was an auto-increment column, the id that the deleted row had will NOT be reused.

Deleting many rows

You would use the exact same query to delete many rows, except the WHERE clause would match multiple rows in the same table.

Using the same starting data, if you delete entries where country = 'USA', all of the rows where that's true will be gone.

Deleting everything / truncation

There are two ways to delete everything: using the delete query above, or issuing a truncate. Which one you need depends on a few factors. I've run into problems with truncate while testing functionality where emptying a database table was part of setting up / tearing down the test suite due to locking and other concurrent tests.

DELETE FROM schema.tablename;

It will delete every row from the table one by one. There is no where clause, which means every row is fair game, which means all of them are going to be removed.

A significantly faster way of doing this is issuing a truncate command.

TRUNCATE schema.tablename;

The main difference between the delete everything without where and truncae is that truncate will just delete the entire contents of the table, while the delete will loop over each row one by one and do the deletes on them one by one.

That's everything that you need to know about deletes on a basic level. This also finished the basic operations you can do on data in a table. Let's move on to table joins!