More SQL Please: DELETE

The DELETE statement allows you to delete existing rows from a table.

A DELETE will delete every row from a table unless a WHERE clause is provided.

It is not required to provide a WHERE clause but it is highly recommended to avoid accidentally deleting all the rows in a table. Some database engines will warn or even prevent you from running a table-wide DELETE statement.

It's usually a good idea to run a SELECT statement with the same WHERE clause as the DELETE to check which rows will be deleted before actually performing the delete.

Remember that each example on this website is editable and runnable so you can see the results of your DELETE by querying the contents of the table, i.e.: SELECT * FROM users;

DELETE FROM users
WHERE id = 8;

1 row affected.

Some database engines support the RETURNING clause, which will cause the database to return the rows that were deleted. You can specify the column names to return or * to include all columns.

DELETE FROM users
WHERE id = 8
RETURNING id, email_address;

| id | email_address | |----|---------------------------| | 8 | priya@aminiconsulting.com |

In this example we have two tables that have a relationship with each other; a post must have an associated user that created it.

Remember that when you create a table you can define Constraints that will tell the database to enforce certain rules on a column. In this example we defined a PRIMARY KEY on the id column of the users table and a FOREIGN KEY on the user_id column of the posts table that references the user's id.

When we try to delete a user that still has posts in the database, the deletion fails because of this constraint we added. This prevents the database from having posts that are no longer associated with a user, also known as "orphaned" records. See more in Schema Design.

DELETE FROM users
WHERE id = 1;

1 row affected.