SQL online course

SQL DELETE Statement

Learn how to use SQL DELETE to remove selected rows safely, preview conditions with SELECT, use transactions, and avoid deleting an entire table accidentally.

The SQL DELETE statement removes existing rows from a table. It is a data manipulation statement: it changes the table's data, but normally leaves the table definition, columns, and indexes in place.

DELETE removes complete rows, not individual column values. To change values while keeping rows, use UPDATE. To inspect rows without changing them, use SELECT.

Basic DELETE Syntax

DELETE FROM table_name
WHERE condition;

• DELETE FROM starts the row-removal operation.
• table_name identifies the table whose rows may be removed.
• WHERE condition is a predicate: an expression that evaluates whether each row matches. Only matching rows are deleted.

SQL string values are normally enclosed in single quotes, such as 'John'. Numeric values usually do not need quotes, such as 17777.

The WHERE clause is optional syntactically, but omitting it has a very different and much more dangerous meaning.

Example Employees Table

Assume an employees table with these columns:

The two John Doe rows deliberately have different employee numbers. This illustrates why a person's name may not uniquely identify one row.

Deleting Rows That Match Conditions

To remove employees whose first and last names are both John Doe, combine the conditions with AND:

DELETE FROM employees
WHERE firstName = 'John'
  AND lastName = 'Doe';

This statement removes every row for which both comparisons are true. In the sample data, both John Doe rows are deleted. The Maria Smith and Ravi Patel rows remain.

AND is a logical operator: both parts of the predicate must match. A condition using OR would have a broader scope because a row could match either part.

Preview the Rows First

Use the same predicate in a SELECT query before issuing DELETE:

SELECT employeeNumber, firstName, lastName, email
FROM employees
WHERE firstName = 'John'
  AND lastName = 'Doe';

Inspect the result, including the employee numbers. If two rows appear, the DELETE will normally affect two rows. This preview is one of the simplest ways to catch an overly broad condition.

Deleting Exactly One Record

A primary key is a column, or set of columns, that uniquely identifies each row. When the goal is to delete exactly one employee, use a primary key or another value constrained to be unique:

SELECT *
FROM employees
WHERE employeeNumber = 17777;

DELETE FROM employees
WHERE employeeNumber = 17777;

After the DELETE, a SELECT using that condition should return no row for employee number 17777. The database tool may also report an affected-row count of 1.

If employeeNumber is not declared unique, the value could still match multiple rows. Confirm the table's constraints and preview the result before deleting.

DELETE Without WHERE

A DELETE statement without a WHERE clause removes every row from the target table:

DELETE FROM employees;

This is different from deleting the table itself. The table can usually still receive new rows after a successful DELETE, subject to permissions and constraints.

Verifying a DELETE

1. Run a SELECT with the intended predicate before deletion.
2. Check the returned rows and their identifiers.
3. Run the DELETE statement.
4. Read the affected-row count reported by the database tool.
5. Run SELECT again to confirm that the deleted rows no longer appear.

SELECT *
FROM employees
WHERE employeeNumber = 17777;

Before deletion, this query should show the targeted row. After a successful committed deletion, it should return an empty result. An affected-row count of zero means that no row matched the predicate at the time the statement ran; it does not necessarily indicate a syntax error.

Transactions: Test Before Making the Delete Permanent

A transaction is a unit of database work that can generally be committed or rolled back. COMMIT makes the transaction's changes permanent. ROLLBACK reverses changes that have not been committed.

Where the database supports transactional DELETE and explicit transaction control, test a deletion like this:

START TRANSACTION;

DELETE FROM employees
WHERE employeeNumber = 17777;

SELECT *
FROM employees
WHERE employeeNumber = 17777;

ROLLBACK;

The SELECT inside the transaction should return no row after the DELETE. ROLLBACK then restores the uncommitted deletion. If the result is correct and you intend to keep the change, use COMMIT instead:

START TRANSACTION;

DELETE FROM employees
WHERE employeeNumber = 17777;

SELECT *
FROM employees
WHERE employeeNumber = 17777;

COMMIT;

Transaction syntax and behavior vary between database systems. Some environments use BEGIN instead of START TRANSACTION, and some tools enable auto-commit by default. After a statement is auto-committed or explicitly committed, ROLLBACK generally cannot undo it.

For production work, make sure a current backup and an appropriate recovery plan exist. Validate the database connection, table, predicate, affected-row count, and transaction state before finalizing a destructive operation.

DELETE Compared with Related Statements

DROP is a definition-language operation, while DELETE is intended to remove table rows. UPDATE changes values but does not remove the rows. SELECT only retrieves data and is useful for previewing and validating a DELETE condition.

Common Problems and Fixes

More rows were deleted than expected

The predicate probably used non-unique data, such as a shared first and last name. Run SELECT with the same predicate, then target a primary key or other unique identifier. Remember that all matching rows are affected.

Every row was deleted

The DELETE probably ran without a WHERE clause. If the operation is still uncommitted, use ROLLBACK. If it was committed or auto-committed, restore the data from a backup or use the database's recovery procedures.

Zero rows were deleted

No row met the condition, or the supplied value did not match the stored data. Run SELECT with the same condition, check spelling and data types, review database-specific case-sensitivity rules, and confirm that you are using the intended table and database.

A related-record constraint rejected the deletion

A foreign key may prevent removal of a parent row referenced by another table. Inspect dependent records and follow the intended referential-integrity strategy: remove or reassign dependent rows when appropriate, or use an explicitly designed cascade rule. Do not disable constraints casually.

The deletion cannot be undone

The statement may have been auto-committed or the transaction may already have been committed. Use backups and recovery procedures. For future destructive operations, preview with SELECT and use an explicit transaction where supported.

DELETE Safety Checklist

• Confirm the correct database, schema, and table.
• Write the WHERE predicate carefully.
• Preview matching rows with SELECT.
• Prefer a primary key or verified unique identifier for one-row deletion.
• Check the expected affected-row count.
• Use a transaction and ROLLBACK while testing when supported.
• Use COMMIT only after validation.
• Maintain backups before production changes.

For related SQL concepts, review SQL AND and OR operators, SQL constraints, SQL INSERT INTO, and SQL SELECT LIMIT.