DELETE

Learning Focus

Use this lesson to remove rows from DuckDB tables confidently using DELETE statements.

Basic Syntax

DELETE FROM table_name WHERE condition;

caution

Omitting WHERE deletes all rows from the table. Always confirm the affected rows with a SELECT before executing DELETE.

Examples

Example 1: Delete by Primary Key

DELETE FROM employees WHERE id = 4;

Example 2: Delete by Condition

-- Remove inactive employees
DELETE FROM employees WHERE active = false;

Example 3: Safe Pattern

-- Step 1: Preview
SELECT COUNT(*) FROM employees WHERE hire_date < DATE '2020-01-01';

-- Step 2: Delete
DELETE FROM employees WHERE hire_date < DATE '2020-01-01';

Example 4: Delete Using Subquery

DELETE FROM employees
WHERE id IN (
    SELECT id FROM employees WHERE department = 'HR' AND salary < 60000
);

Example 5: Truncate (Delete All Rows Fast)

-- Removes all rows without logging individual deletions
DELETE FROM employees;
-- or equivalent:
TRUNCATE employees;  -- supported in newer DuckDB versions

Common Pitfalls

Pitfall	Consequence	Prevention
No WHERE clause	Entire table wiped	Always select first to confirm row count
Assuming soft-delete by default	Rows permanently removed	Use active = false pattern for soft deletes
Deleting while iterating	Unexpected results	Use subquery or staging table pattern

Quick Reference

DELETE FROM t WHERE id = 1;
DELETE FROM t WHERE active = false;
DELETE FROM t WHERE col IN (SELECT col FROM other);

What's Next

• Next: LIMIT - Restrict result set size.
• Module Overview - Return to this module index.