How to Delete Rows in Redshift

How do I delete rows in Amazon Redshift?

DELETE removes one or more rows from a Redshift table using an optional WHERE filter.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why delete rows instead of dropping a table?

DELETE keeps the table structure, permissions, and dependent objects intact while removing only the unwanted data. This is safer than DROP and more flexible than TRUNCATE when you need fine-grained control.

What is the fastest way to delete rows in Redshift?

Filter on sort key and distribution key columns so Redshift can prune blocks quickly. Batch large deletions, then run VACUUM DELETE to reclaim space and sort remaining rows.

How does the DELETE syntax work?

DELETE targets a table, applies an optional alias, and removes rows that satisfy the WHERE clause. If you omit WHERE, every row is marked for deletion and later vacuumed.

Basic pattern

DELETE FROM schema.table_name [AS alias] WHERE condition;

Can I delete with a join?

Yes. Use a USING clause to reference another table, or embed a correlated sub-query inside WHERE. Both methods allow multi-table criteria.

Join example

DELETE FROM Orders o USING Customers c WHERE o.customer_id = c.id AND c.email LIKE '%@test.com';

Do I need VACUUM after DELETE?

Rows are only marked as deleted. Run VACUUM DELETE (or VACUUM FULL) during low-traffic windows to free disk space and keep query plans efficient.

Best practices for production

1) Always qualify the table with schema. 2) Test the WHERE clause with SELECT first. 3) Break very large deletes into smaller commit-sized chunks. 4) Schedule vacuum and analyze afterward.

Complete working example

See the next section for a full DELETE statement that removes orphaned orders.

Why How to Delete Rows in Redshift is important

How to Delete Rows in Redshift Example Usage


-- Remove order items linked to non-existent products
DELETE FROM OrderItems oi
USING Products p
WHERE oi.product_id = p.id
  AND p.stock = 0;

How to Delete Rows in Redshift Syntax


DELETE [FROM] <schema>.<table_name> [AS alias]
[USING <schema>.<other_table> [, ...]]
[WHERE <condition>]
[RETURNING * | column_list];

-- Options explained
-- USING: reference one or more tables to build complex predicates
-- WHERE: filters rows to delete; omit to remove all rows
-- RETURNING: outputs deleted rows for verification (Redshift RA3 preview)

Common Mistakes

Omitting WHERE when you intended a filtered delete. Always run SELECT with the same predicate first to confirm the affected row count.
Forgetting to vacuum after massive deletes, leaving ghost rows that bloat storage and slow queries. Schedule VACUUM DELETE routinely.