tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

SQL: DELETE Statement

Learn how to use the SQL DELETE statement with syntax, examples, and practice exercises.

Description

The SQL DELETE statement is a used to delete a one or more records from a table.

Syntax

The syntax for the SQL DELETE statement is:

DELETE FROM table
WHERE conditions;

Parameters or Arguments

table is the table that you wish to delete records from.

conditions are conditions that must be met for the records to be deleted.

Note

  • You do not need to list fields in the SQL DELETE statement since you are deleting the entire row from the table.

Example - With One condition

Let's look at an example showing how to use the SQL DELETE statement.

For example:

DELETE FROM suppliers
WHERE supplier_name = 'IBM';

This SQL DELETE example would delete all records from the suppliers table where the supplier_name is IBM.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL SELECT statement before performing the delete.

SELECT count(*)
FROM suppliers
WHERE supplier_name = 'IBM';

Example - With Two conditions

Let's look at a SQL DELETE example, where we just have two conditions in the SQL DELETE statement.

For example:

DELETE FROM products
WHERE units >= 12
AND category = 'Clothing';

This SQL DELETE example would delete all records from the products table where the units is greater than or equal to 12 and the category is Clothing.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL SELECT statement before performing the delete.

SELECT count(*)
FROM products
WHERE units >= 12
AND category = 'Clothing';

Example - Using SQL EXISTS Clause

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the SQL FROM clause when you are performing a delete, you can use the SQL EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customers.customer_name = 'IBM' );

This SQL DELETE example would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.

If you wish to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customers.customer_name = 'IBM' );

Frequently Asked Questions

Question: How would I write a SQL DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this for your SQL DELETE statement:

DELETE FROM TableA
WHERE NOT EXISTS
  ( SELECT *
    FROM TableB
     WHERE TableA.field1 = TableB.fieldx
     AND TableA.field2 = TableB.fieldz );

Practice Exercise #1:

Based on the employees table, delete all employee records whose salary is greater than $40,000:

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #1:

The following SQL DELETE statement would delete these records from the employees table:

DELETE FROM employees
WHERE salary > 40000;

Practice Exercise #2:

Based on the suppliers table, delete the supplier record whose supplier_id is 5001 and supplier_name is Apple:

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Solution for Practice Exercise #2:

The following SQL DELETE statement would delete this record from the suppliers table:

DELETE FROM suppliers
WHERE supplier_id = 5001
AND supplier_name = 'Apple';

Practice Exercise #3:

Based on the customers and old_customers table, delete from the customers table all records that exist in the old_customers table (matching the customer_id field from the customers table to the old_customer_id field in the old_customers table).

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE old_customers
( old_customer_id number(10) not null,
  old_customer_name varchar2(50) not null,
  old_city varchar2(50),
  status varchar2(20),
  CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id)
);

Solution for Practice Exercise #3:

The following SQL DELETE statement would be the solution (using the SQL EXISTS clause) that would delete the records from the customers table:

DELETE FROM customers
WHERE EXISTS
  ( SELECT old_customers.old_customer_id
    FROM old_customers
    WHERE old_customers.old_customer_id = customers.customer_id );