0Pricing
SQL Academy · Lesson

DELETE with USING and Safe Patterns

Delete rows joined to other tables with DELETE ... USING, and use transactions + SELECT first to verify before destructive operations.

DELETE with USING and Safe Patterns is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Basic DELETE

Remove matching rows:

DELETE FROM users WHERE id = 1;

Always Test with SELECT First

Before any DELETE, run the same WHERE with SELECT to confirm the row count:

SELECT COUNT(*) FROM orders
WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '1 year';

DELETE in a Transaction

Wrap destructive operations in a transaction so you can ROLLBACK:

BEGIN;
DELETE FROM orders WHERE status = 'cancelled' AND ...;
SELECT COUNT(*) FROM orders;       -- sanity check
-- COMMIT;  or ROLLBACK;

DELETE Without WHERE Is a Disaster

DELETE FROM users; empties the table. There's no Undo button. Be paranoid; one extra second of typing a WHERE is cheap insurance.

DELETE … USING (PostgreSQL JOIN-Delete)

Delete rows in one table based on another:

DELETE FROM orders o
USING users u
WHERE u.id = o.user_id
  AND u.is_banned = true;

DELETE with Subquery

Standard alternative:

DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_banned);

Foreign Key Cascades

If orders.user_id has ON DELETE CASCADE, deleting a user automatically deletes their orders. Convenient but dangerous — use sparingly.

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;

Soft Deletes

Many apps don't actually delete rows — they set a deleted_at timestamp:

UPDATE users SET deleted_at = NOW() WHERE id = $1;

-- Every query then has:
WHERE deleted_at IS NULL

Cleaning Up Old Data

For periodic cleanup of large tables, delete in batches to avoid long-running transactions:

DELETE FROM events
WHERE id IN (
  SELECT id FROM events
  WHERE created_at < NOW() - INTERVAL '90 days'
  LIMIT 10000
);
-- run this in a loop until it returns 0 rows

TRUNCATE: When Speed Matters

If you really want to wipe a table, TRUNCATE is faster than DELETE because it doesn't scan rows. But it doesn't fire ROW triggers and can't be rolled back in all setups:

TRUNCATE TABLE staging_orders;
TRUNCATE TABLE staging_orders RESTART IDENTITY CASCADE;

RETURNING from DELETE

Get the deleted rows back — useful for archiving:

WITH deleted AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

Recap

DELETE is the most dangerous statement. Defensive patterns:

  • SELECT first
  • BEGIN; ... ; ROLLBACK as a dry-run
  • Batch big deletes
  • Prefer soft deletes for user-visible data
  • TRUNCATE only on staging tables

Quick Check

You ran DELETE FROM users; with no WHERE. What do you do?

Frequently asked questions

Is the “DELETE with USING and Safe Patterns” lesson free?

Yes — the full text of “DELETE with USING and Safe Patterns” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “DELETE with USING and Safe Patterns”?

Delete rows joined to other tables with DELETE ... USING, and use transactions + SELECT first to verify before destructive operations. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “DELETE with USING and Safe Patterns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. INSERT with Multiple Rows
  2. UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)
  3. UPDATE with FROM and JOIN-style Updates
  4. DELETE with USING and Safe Patterns
← Back to SQL Academy