The DELETE statement removes rows from a table. It can:

• Remove all rows from a table
• Remove specific rows based on conditions
• Remove related records across multiple tables

Important Concepts:

• DELETE is permanent - there's no undo
• WHERE clause determines which rows to delete
• Foreign key constraints may prevent deletion
• Consider using transactions for safety

Best Practices:

• Always use WHERE unless you really want to delete everything
• Test DELETE queries with SELECT first
• Back up important data before large deletions
• Consider soft deletes for recoverable data

Let's start with a simple deletion. We'll remove a specific customer from the customers table.

Write a query that:

• Uses DELETE FROM customers
• Removes the customer with id = 1

Remember:

• DELETE without WHERE deletes ALL rows
• Always double-check your WHERE condition
• Consider running a SELECT first to verify

Often you'll need to delete rows that match multiple criteria.

Delete all products that are:

• Out of stock (in_stock = 0)
• Created more than a year ago (created_at < date('now', '-1 year'))

Tips for Complex Conditions:

• Use AND to combine multiple conditions
• Test with SELECT first to verify the rows
• Consider the order of conditions for performance
• Use parentheses to group conditions clearly

Sometimes you need to delete rows based on data in other tables.

Delete all orders that:

• Were placed by customers from a specific country
• Have no items (total_amount = 0)

Write a query that:

• Uses a subquery to find customers from 'USA'
• Deletes their empty orders

Key Points:

• Subqueries make DELETE more powerful
• They can reference other tables
• Consider indexes for performance
• Test subqueries separately first

When tables are related through foreign keys, deleting a parent row can affect child rows.

Delete a customer and ensure all their orders are removed:

• Delete the customer with id = 2
• Their orders should be deleted automatically
• This requires CASCADE on the foreign key

Important Safety Tips:

• Understand CASCADE implications
• Check related data before deleting
• Use transactions for safety
• Consider backing up first

Some databases support LIMIT with DELETE to restrict the number of rows affected.

Delete the 5 oldest completed orders:

• Target orders with status = 'completed'
• Order by created_at ascending
• Limit to 5 rows

Note: While SQLite doesn't support LIMIT directly with DELETE, you can achieve similar results using subqueries or rowid.

Best Practices:

• Use ORDER BY for predictable results
• Consider performance on large tables
• Test the selection criteria first
• Use transactions for safety