Skip to main content
Database Administration & Troubleshooting

Delete Row SQL: Syntax, Clauses, Examples & Troubleshooting

5 min read

delete row sql is the SQL DELETE statement used to remove one or more rows from a table based on a WHERE condition, preserving the table structure and schema.

DELETE FROM Employees WHERE EmployeeID = 5;

When to use DELETE

DELETE is a Data Manipulation Language (DML) operation that removes rows permanently unless wrapped in an explicit transaction. Use it to purge obsolete data, enforce retention policies, or correct records. Unlike TRUNCATE, DELETE logs each row and can fire triggers. It supports the OUTPUT clause for auditing and can be combined with a CTE for complex filtering.

Tested on SQL Server 2022 (16.x) with AdventureWorks database.

Syntax

-- Standard SQL DELETE syntax
DELETE FROM table_name WHERE condition;

-- Transact-SQL extensions (SQL Server)
DELETE [TOP (expression) [PERCENT]] [FROM] table_or_view_name 
[FROM table_source] 
[WHERE {search_condition | CURRENT OF cursor_name}]
[OPTION (query_hint [, ...n])];

-- With common table expression
WITH cte_name (column_list) AS (SELECT ...)
DELETE FROM table_name 
FROM table_name AS alias
JOIN cte_name ON condition;

SQL DELETE Command Cheat Sheet

Action SQL Command Key Clause Description
Delete single row by primary key DELETE FROM Employees WHERE EmployeeID = 5; WHERE Removes one row where ID equals 5.
Delete multiple rows with IN list DELETE FROM Categories WHERE CategoryID IN (2,3,5); IN Deletes rows matching any of the listed IDs. Each row must satisfy one condition; AND between IDs returns zero rows.
Delete all rows DELETE FROM Employees; No WHERE Removes every row; transaction log grows. Prefer TRUNCATE if rollback not needed.
Delete with a join (SQL Server) DELETE e FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID WHERE d.Name = 'Development'; Second FROM + JOIN Deletes from Employees based on a condition in Departments.
Delete using a CTE WITH OldOrders AS (SELECT * FROM Orders WHERE OrderDate < '2020-01-01') DELETE FROM OldOrders; WITH + CTE Deletes rows from a CTE that filters the base table.
See also  CHARINDEX SQL Server: T-SQL Function Reference, Syntax, and

Clauses (Options)

The DELETE statement uses clauses rather than flags. The table below lists the main clauses in Transact-SQL.

Clause Type Description
TOP (n) [PERCENT] Optional Limits the number of rows deleted. Use PERCENT for relative count. ORDER BY is not supported directly; use a derived table with ROW_NUMBER().
FROM Optional First FROM specifies the target table; second FROM enables joins to delete rows based on relationships.
WHERE Optional Filters rows to delete. If omitted, all rows are deleted. Use CURRENT OF to delete the current row of a cursor.
OUTPUT Optional Returns deleted rows as a result set. Can insert into a table for auditing. Example: OUTPUT DELETED.* INTO AuditTable.
WITH Optional Specifies a common table expression for a temporary named result set used in the DELETE.
OPTION Optional Applies query hints such as MAXDOP or RECOMPILE. Use sparingly in production.

Usage Examples

Delete a single row by unique condition

DELETE FROM Employees WHERE EmployeeID = 7;

Deletes the row with EmployeeID 7. If no row matches, zero rows are affected. Wrap in BEGIN TRAN / ROLLBACK to test before commit.

Delete multiple rows with an IN clause

DELETE FROM Categories WHERE CategoryID IN (2, 3, 5);

Removes three rows at once. Each row must match exactly one of the IN values. Avoid using AND between IDs—that returns zero rows because no single row can satisfy two different IDs.

Delete rows based on a join condition (SQL Server)

DELETE e
FROM Employees AS e
JOIN Departments AS d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Development';

Deletes all employees whose department is 'Development'. The second FROM clause enables joining to the Departments table. This is a Transact-SQL extension; standard SQL uses a subquery with IN.

See also  Oracle Compare Dates: Syntax, Examples, and Expert Guide

Troubleshooting & Common Errors

Error Message Root Cause Resolution Command
The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Employee". Foreign key references exist from other tables (e.g., Orders). DELETE FROM Orders WHERE EmployeeID = 5; then retry.
Cannot insert duplicate key in object 'AuditTable'. OUTPUT clause tries to insert a duplicate row that already exists in the target audit table. Use a different key or modify OUTPUT to avoid duplicates.
Zero rows affected, but expected to delete. WHERE clause uses incorrect column names or data types (e.g., string instead of integer). Check data types: DELETE FROM Categories WHERE CategoryID = '2'; — use WHERE CategoryID = 2;
Deleting all rows without WHERE. Accidental full table delete. Use BEGIN TRAN; DELETE FROM Table; ROLLBACK; for testing. For production, enforce WHERE via application code.

Advanced: DELETE Across Database Engines

Although the core DELETE statement is part of SQL standard, each engine offers syntax extensions. The following table highlights key differences.

Feature SQL Server (T-SQL) PostgreSQL MySQL
Delete with JOIN Second FROM clause with JOIN USING clause in DELETE Multi-table DELETE syntax
Delete with TOP TOP (n) or TOP (n) PERCENT Not supported; use LIMIT with subquery LIMIT clause in DELETE
Delete with OUTPUT OUTPUT DELETED.* RETURNING clause Not directly supported
Delete with CTE WITH + DELETE WITH + DELETE WITH + DELETE (MySQL 8.0+)

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE for removing rows in SQL?

DELETE removes rows individually, logs every operation, and can be filtered via WHERE. It is DML, supports triggers, and preserves table structure. TRUNCATE is DDL, resets auto-increment counters, and is faster for wiping all rows. Use DELETE for granular control; TRUNCATE for bulk removal without rollback needs.

See also  psql Date Difference: Syntax, Examples, and Best Practices

When should I use the WHERE clause in a DELETE statement?

Always use WHERE unless you intend to remove every row. WHERE filters rows via conditions like id = 100 or created_at < '2023-01-01'. For safety, use SELECT with the same WHERE first to preview affected rows.

How do I fix the "FOREIGN KEY constraint failed" error when deleting a row?

Delete or update child rows referencing the target row first, or enable ON DELETE CASCADE on the foreign key definition. The error occurs when parent row is referenced. Use DELETE FROM child_table WHERE parent_id = ?; then delete parent. Alternatively, alter table to add CASCADE: ALTER TABLE child ADD CONSTRAINT fk_cascade FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;

Does the DELETE command work identically across all major cloud SQL databases (AWS RDS, Azure SQL, GCP Cloud SQL)?

Yes, standard DELETE syntax is consistent across MySQL, PostgreSQL, SQL Server, and Oracle variants on all cloud platforms. While ANSI SQL DELETE is identical, each engine has quirks: MySQL permits DELETE ... LIMIT, SQL Server requires TOP, PostgreSQL uses RETURNING. Cloud providers do not modify SQL syntax.

What is the fastest way to delete a large number of rows (millions) from a table in PostgreSQL?

Use DELETE FROM table WHERE condition in batches of 10,000 rows per transaction, or use TRUNCATE if removing all rows. For partial deletion, loop with psql -c "DELETE FROM table WHERE condition LIMIT 10000" until rollback is negligible. Consider partitioning and DROP partitions for even better performance.