SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Lesson 8.3: The DELETE Statement

In the previous lesson, we learned how to modify records using UPDATE. Now let's look at how to remove unnecessary or outdated rows with the DELETE statement. This is an important DML command that should be used especially carefully, because deleted data cannot always be easily recovered.

Basic Syntax

DELETE FROM table_name
WHERE condition;
  • DELETE FROM table_name — specifies the table from which rows will be removed.
  • WHERE condition — determines which rows will be deleted.

If WHERE is omitted, all rows in the table will be deleted.

Important Rules

  • Always check the WHERE clause: a mistake in the condition can delete the wrong data or too many rows.
  • Run SELECT first: before using DELETE, it is good practice to run SELECT with the same condition and make sure the result is correct.
  • The table structure remains: DELETE removes only data, not the table itself or its columns.
  • Related data matters: if foreign keys are defined, the database may prevent deletion of rows that are referenced by other tables.
  • Transactions: in production systems, it is safer to run important deletions inside a transaction.

Examples

Example 1: Deleting one row

Let's remove one customer by their identifier:

DELETE FROM customer
WHERE customer_id = 1;

Note: thanks to WHERE customer_id = 1, only one specific row will be deleted.

Example 2: Deleting multiple rows by condition

Let's delete payment records made before a certain date:

DELETE FROM payment
WHERE payment_date < '2005-05-25';

Result: all rows in payment with a payment date earlier than the specified date will be removed.

Example 3: Deleting with a subquery

Sometimes you need to delete rows based on a condition from another table. For example, let's remove payments made by inactive customers:

DELETE FROM payment
WHERE customer_id IN (
    SELECT customer_id
    FROM customer
    WHERE active = 0
);

Result: all payments of customers marked as inactive will be deleted.

Example 4: Deleting all rows from a table

If you need to completely clear a table, DELETE can also be used without WHERE:

DELETE FROM temp_import;

Note: the temp_import table will still exist, but all of its rows will be removed.

Checking Before Deleting

A good practice is to first inspect which rows will be affected:

-- First check the rows
SELECT customer_id, first_name, last_name, active
FROM customer
WHERE active = 0;

-- Only after verifying, run the DELETE
DELETE FROM customer
WHERE active = 0;

This approach helps avoid accidentally deleting extra data.

When DELETE Is Especially Useful

  • cleaning temporary or test data;
  • removing outdated records by date;
  • deleting rows that no longer meet business rules;
  • preparing tables for reloading data.

Key takeaways from this lesson:

  • DELETE removes existing rows from a table.
  • Without WHERE, all rows in the table will be deleted.
  • Before deleting, it is advisable to run SELECT with the same condition.
  • DELETE keeps the table structure intact — only the data is removed.
  • In important scenarios, it is safer to use transactions and consider foreign keys.