SQL DELETE Statement Explained with Examples (2026)

The statement runs, four rows come back as affected, and you meant to remove one. DELETE removes every row that matches its WHERE clause, and every row in the table when the WHERE clause is missing. It leaves the table, its columns, and its indexes where they are. So the way to be sure of a DELETE is to run the matching SELECT first and count what comes back.

What the SQL DELETE statement does

The whole statement is two lines:

DELETE FROM table_name
WHERE condition;

The WHERE clause is optional to the parser and not optional to you. DELETE FROM orders; is accepted and empties the table. Examples on this page run against a pair of tables, customers and their orders:

CREATE TABLE customers (
    customer_id     INT PRIMARY KEY,
    name            VARCHAR(50),
    customer_status VARCHAR(20)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    status      VARCHAR(20)
);

INSERT INTO customers VALUES
    (1, 'Ada',   'active'),
    (2, 'Grace', 'inactive'),
    (3, 'Linus', 'inactive');

INSERT INTO orders VALUES
    (10, 1, 40.00, 'shipped'),
    (11, 2, 12.50, 'cancelled'),
    (12, 3, 99.90, 'shipped'),
    (13, 1,  7.00, 'cancelled'),
    (14, 2, 25.00, 'cancelled');

Turn the DELETE you mean to run into a SELECT with the same FROM and the same WHERE, and read what it returns:

SELECT * FROM orders
WHERE status = 'cancelled';

Three rows, and you can see which three. That is the entire safety habit, and it costs one keystroke to swap SELECT * for DELETE. The SQL WHERE clause covers the conditions themselves.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Delete a single row

A single-row delete targets the primary key, because the primary key is the one condition that cannot match a second row by accident:

DELETE FROM orders
WHERE order_id = 12;

SELECT * FROM orders ORDER BY order_id;

Order 12 is gone and the other four rows are untouched. A filter on a column that is not unique carries a weaker guarantee. WHERE order_total = 99.90 would have removed order 12 today and any number of rows tomorrow.

Delete multiple rows

A condition that matches a group removes the group in one statement:

DELETE FROM orders
WHERE status = 'cancelled';

SELECT * FROM orders ORDER BY order_id;

The three rows the preview query listed are the three that went. Any operator a WHERE clause accepts works here as well: order_total BETWEEN 40 AND 50, status IN ('cancelled', 'returned'), order_date < DATE '2024-01-01'.

Scale changes the shape of the problem. A delete of several million rows runs as one transaction, holds its locks until it finishes, and writes every removed row to the transaction log, so cleanup jobs are written to run in batches. MySQL 8.4 has a clause for it: "the MySQL-specific LIMIT row_count clause for DELETE specifies the maximum number of rows to be deleted", and you repeat the statement until it reports fewer rows than the limit.

DELETE FROM orders
WHERE status = 'cancelled'
LIMIT 1000;

SQL Server writes the same idea as DELETE TOP (1000) FROM orders WHERE status = 'cancelled'. PostgreSQL 17 has no such clause, and its documentation says so plainly before showing the replacement: "While there is no LIMIT clause for DELETE, it is possible to get a similar effect" by selecting a batch of row identifiers first.

WITH delete_batch AS (
    SELECT ctid
    FROM orders
    WHERE status = 'cancelled'
    ORDER BY order_id
    FOR UPDATE
    LIMIT 1000
)
DELETE FROM orders
USING delete_batch
WHERE orders.ctid = delete_batch.ctid;

Delete with subqueries and joins

The rows to remove are often chosen by a value in another table. Two orders go back in first:

INSERT INTO orders VALUES
    (11, 2, 12.50, 'cancelled'),
    (12, 3, 99.90, 'shipped');

A subquery in the WHERE clause reads the other table and hands back a list of values to match:

DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE customer_status = 'inactive'
);

SELECT * FROM orders ORDER BY order_id;

Grace and Linus are the inactive customers, so their orders 11 and 12 went, and Ada's order stayed. Write this with IN rather than NOT IN where you can. A NOT IN whose subquery returns even one NULL matches nothing at all, and the delete then silently removes zero rows, for the reason SQL NULL values explains.

The same delete can be written as a join, and the syntax is where the engines part company:

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.customer_id
  AND c.customer_status = 'inactive';

DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_status = 'inactive';

Both remove the same two orders as the subquery above. The join form is worth the extra syntax when you need columns from the second table in the condition, and MySQL 8.4 asks for one thing in return: "You cannot use ORDER BY or LIMIT in a multiple-table DELETE", so a batched cleanup has to stay single-table.

Delete duplicate rows

Keeping one row of each duplicate group takes a number on each row inside the group:

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(50)
);

INSERT INTO products VALUES
    (1, 'Keyboard'),
    (2, 'Mouse'),
    (3, 'Keyboard'),
    (4, 'Keyboard');

SELECT product_id, product_name,
       ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY product_id) AS row_num
FROM products
ORDER BY product_id;

PARTITION BY product_name restarts the count for each name, and ORDER BY product_id decides which row of a group gets number 1. Read that table before deleting anything: every row numbered 1 survives, every higher number goes.

In SQL Server the numbering can go in a common table expression and the delete can target it, because a CTE there "must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement":

WITH ranked_products AS (
    SELECT product_id,
           ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY product_id) AS row_num
    FROM products
)
DELETE FROM ranked_products
WHERE row_num > 1;

SELECT * FROM products ORDER BY product_id;

PostgreSQL 17 deletes from a table rather than from a CTE, so the same job is a self-join that keeps the lowest id of each name:

DELETE FROM products p
USING products q
WHERE p.product_name = q.product_name
  AND p.product_id > q.product_id;

That removes products 3 and 4 and leaves the two rows above.

DELETE vs TRUNCATE vs DROP

Three statements remove data, and only one of them takes a condition:

TRUNCATE is the fast way to empty a table, and PostgreSQL 17 says where the speed comes from: it "has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster", and it returns the disk space at once instead of leaving it for a later VACUUM. That skipped scan is also what you give up. DELETE visits each row, so it can fire row-level triggers and check foreign keys one row at a time, and TRUNCATE cannot.

Reach for DELETE when you are removing some of the rows, for TRUNCATE when you want every row gone and the table kept, and for DROP TABLE when the table itself should stop existing. If what you actually want is to change rows rather than remove them, the SQL UPDATE statement is the one to read next.

How to delete rows safely

A transaction turns the preview and the delete into one reversible step. Run the block, read the table, then decide:

BEGIN;

DELETE FROM orders
WHERE status = 'cancelled';

SELECT * FROM orders ORDER BY order_id;

ROLLBACK;

ROLLBACK puts every deleted row back. Swap it for COMMIT once the result looks right.

Check what references the table before deleting from the parent side. A foreign key declared plainly makes the database refuse to delete a customer that still has orders. Declared ON DELETE CASCADE, the delete goes through and takes the orders with it. Declared ON DELETE SET NULL, the orders survive with an empty customer_id:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE;

All three are correct behaviors, and which one you get was decided when the table was created, not when you ran the delete. What Is a Foreign Key? covers the choice, and foreign keys in PostgreSQL covers the PostgreSQL syntax for it.

Keep a copy of what you removed when the rows matter. PostgreSQL 17 returns the deleted rows from the statement itself:

DELETE FROM orders
WHERE order_id = 10
RETURNING *;

SQL Server does the same with the OUTPUT clause, which "returns information from, or expressions based on, each row affected", written as DELETE FROM orders OUTPUT DELETED.* WHERE order_id = 10. Copying the rows into an archive table before the delete works everywhere and is covered in SQL INSERT INTO SELECT.

Run DELETE statements in DbSchema

Two questions decide whether a delete is safe, and both are easier to answer when you can see the schema. DbSchema connects to the database, reverse-engineers it, and draws the tables as an interactive diagram with a line for every foreign key, so the child tables that reference the one you are deleting from are in front of you before you write the statement.

In the SQL Editor, opened from the Editors menu, the loop is short. Put the cursor on the SELECT and click Execute Query to read the rows you are about to remove. Move to the DELETE and click Execute Query again. Then click Commit to keep the change or Rollback to undo it, which is the transaction above with buttons instead of keywords.

The second question is which child rows the delete would reach. The Relational Data Editor, part of DbSchema Pro, opens the parent table and its children side by side. Selecting a row in the parent refilters every child pane to the rows that reference it, and the refilter cascades as many levels as the foreign keys go.

On a production database, tick Read Only Connection on the Settings tab of the connection dialog and DbSchema opens that connection in read-only mode, so the database refuses every change made through it. Keep one read-only connection for looking and a second one for the statements you intend to run.

Write the SELECT, count the rows, then change one word. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run the pair in the SQL Editor with Commit and Rollback in reach. Connecting, the diagram, the SQL Editor, and Read Only Connection are in the free Community Edition.

FAQ

Can I use DELETE without WHERE?

The statement is valid and it empties the table, one row at a time, firing whatever triggers the table carries. Nothing asks you to confirm, so the missing WHERE clause is the single most expensive typo in SQL.

Can I roll back a DELETE statement?

Inside an open transaction, ROLLBACK restores every deleted row. After COMMIT, or on a connection in auto-commit mode where each statement commits itself, the rows are gone and only a backup brings them back.

Does DELETE reset identity or auto-increment values?

DELETE removes rows and leaves the counter where it is, so the next insert continues from the highest value already used. TRUNCATE can reset it: PostgreSQL 17 does it only when you ask, with TRUNCATE ... RESTART IDENTITY, which "automatically restarts sequences owned by columns of the truncated table".

Is DELETE slower than TRUNCATE?

On a table you are emptying completely, yes, and the skipped table scan described above is where the whole difference comes from. On a table where you are removing part of the rows the comparison does not arise, because TRUNCATE takes no condition.