🗑️ DELETE = Remove Rows
Data needs removal. DELETE removes rows. Be careful — without WHERE, deletes all rows. Use transactions.
📝 Basic DELETE
-- Delete specific row DELETE FROM users WHERE id = 123; -- Delete multiple rows DELETE FROM users WHERE status = 'inactive'; -- Delete all rows (careful!) DELETE FROM users; -- Delete with JOIN (PostgreSQL) DELETE FROM orders o USING users u WHERE o.user_id = u.id AND u.status = 'inactive'; -- Delete with RETURNING (PostgreSQL) DELETE FROM users WHERE id = 123 RETURNING *;
🎯 TRUNCATE (Faster)
-- TRUNCATE (removes all rows, faster than DELETE) TRUNCATE TABLE users; -- TRUNCATE with CASCADE (removes related) TRUNCATE TABLE users CASCADE; -- TRUNCATE vs DELETE -- TRUNCATE: Faster, resets identity, no triggers -- DELETE: Slower, can use WHERE, triggers fire
💡 Safety Tips
- Always use WHERE (or you’ll delete ALL rows!)
- Test with SELECT first (see what will be deleted)
- Use transactions (BEGIN; …; COMMIT;)
- Backup before mass deletion
- Consider soft delete (mark as deleted, don’t remove)
“Forgot WHERE, deleted all users. Disaster. Now always backup and test. DELETE is permanent.”
