LuisLlamas Logo
eliminacion-datos-delete-sql

Data Deletion with DELETE in SQL

  • 4 min
  • Beginner

In the data lifecycle, there comes a time when certain information is no longer needed. Sometimes relationships break, products are discontinued, and users unsubscribe.

To delete records from a table, we use the DELETE statement.

This statement is used to delete one or more rows from a table in a database.

If with UPDATE I told you to be careful, with DELETE I ask for panic.

An accidental deletion is irreversible (time to go to the backup, if you have one).

Basic Syntax

The DELETE statement is deceptively simple. We only need to say from which table we want to delete, and from it, which rows.

DELETE FROM TableName
WHERE Condition;
Copied!
  • TableName: Specifies the table from which rows will be deleted.
  • Condition: Defines which rows will be deleted.

For example, to delete the customer with ID 50:

DELETE FROM Customers
WHERE CustomerID = 50;
Copied!

Here we do not specify columns. DELETE deletes entire rows. You cannot delete “only this and that cell”.

If you want to delete specific data but keep the row, you must use UPDATE to set it to NULL (or zero, or empty, if it’s not nullable).

The Apocalypse of the Forgotten WHERE

Just in case it wasn’t clear with the sign before EXTREME CAUTION.

If you execute a DELETE without a WHERE clause, SQL Server will understand that your intention is to delete ALL rows from the table.

-- ☢️ CATASTROPHIC ZONE
-- This will leave the Customers table empty in milliseconds
DELETE FROM Customers;
Copied!

Just like with UPDATE. It won’t ask you. It won’t be polite. There’s no recycle bin. It will delete it, and that’s it, it’s over 🚨.

Just like with UPDATE, it’s good practice to first write a SELECT with the condition to verify what you are going to delete, before changing it to a DELETE and executing it definitively.

🎵 Don’t forget to puuut the wheeeere in the deleteeee froooom 🎵

DELETE Based on Other Tables

Sometimes we want to delete records based on information that resides in another table. Imagine we want to delete all Orders from customers who are from “Teruel”.

In standard ANSI SQL we would have to use a subquery (WHERE CustomerID IN (SELECT…)). But T-SQL allows us a much more readable syntax by using JOIN directly in the deletion.

DELETE P
FROM Orders P
INNER JOIN Customers C ON P.CustomerID = C.CustomerID
WHERE C.City = 'Teruel';
Copied!

Notice the detail:

  1. We write DELETE P (using the alias of the table we want to clean).
  2. We use FROM and JOIN to relate the tables.
  3. We filter by the column of the joined table.

Referential Integrity and Deletion Blocking

If you try to delete a Customer who has associated Orders, and there is a Foreign Key constraint, SQL Server will stop the operation and give you an error.

The DELETE statement conflicted with the REFERENCE constraint…

This is where the Foreign Keys we defined in Module 2 prove their value.

This happens to protect data consistency. We cannot leave orders “orphaned”. To be able to delete that customer, you have two options:

  1. Manual: Delete their orders first and then the customer.
  2. Cascade: If the FK was configured with ON DELETE CASCADE, when deleting the customer, SQL Server will automatically delete all their orders. (Use this with extreme caution).

Logical Deletion vs Physical Deletion

In the real world, especially in enterprise systems, we rarely use DELETE.

Because data is valuable. Deleting something means losing its history and sometimes breaking things.

What if the customer comes back? What if we need to audit what they bought a year ago? What about all related records?

Instead, we apply the Logical Deletion (Soft Delete) pattern. That is, we use a field to know if the row is used or not (but we don’t delete it).

  1. We add a column to the table, called Active or DeletionDate.
  2. Instead of DELETE, we do an UPDATE to mark it as inactive.

For example,

-- Instead of deleting it physically:
-- DELETE FROM Employees WHERE EmployeeID = 10;

-- We deactivate it logically:
UPDATE Employees
SET Active = 0, DeletionDate = GETDATE()
WHERE EmployeeID = 10;
Copied!

Then, in all our queries, we remember to filter by WHERE Active = 1. This way we maintain the history and allow “undoing” the deletion simply by changing the bit again.

On this page