HOME

DELETE statement in SQL

DELETE is the DML command to delete records from database tables.

The syntax of the statement is as follows:

DELETE FROM tableName WHERE conditions;

As with the UPDATE statement, it is very important to take into account the WHERE clause, to prevent the changes in the table from propagating to all the records, eliminating them in their entirety.

In the case of wanting to delete all the records of a table, the best option is to use the TRUNCATE statement since it is faster and more efficient, using less system and transaction log resources.

To exemplify how the DELETE statement works, we are going to delete some records from the Customers and Orders tables.

After each DELETE, we will leave the INSERT INTO scripts, to be able to restore the records.

In this example, we intend to remove the client Paul:

We execute DELETE and it will tell us that 1 row has been affected. If we try to find that record, we will see that it has disappeared from our Customers table:

DELETE FROM Customers
WHERE customerId = 7;

This is the script to insert the record back.

INSERT INTO Customers
    (customerId,
     [name],
     [lastName],
     [address],
     bankAccount)
VALUES
    (7,
    'Paul',
    'Williams',
    '2696 Ocello Street',
    998766549);

We remind you that in order to specify the value of a field with the IDENTITY property, we must execute that statement before the INSERT INTO:

SET IDENTITY_INSERT Customers ON;

We must pay special attention mainly to key constraints. If, for example, we wanted to delete the client Mavin with customerId = 2, the execution of the statement will return the following error:

DELETE FROM Customers WHERE customerId = 2;

The DELETE statement conflicted with the REFERENCE constraint “fk_customers_orders”. The conflict occurred in database “store”, table “dbo.Orders”, column ‘customerId’.

To delete Mavin, we would have to previously delete the records in the Orders table associated with said client:

DELETE FROM Orders WHERE customerId = 2;

The execution will indicate that 2 rows have been affected, and we can now eliminate the client Mavin, since hes customerId will not be present in the Orders table.

Here are the scripts to re-insert the records from the Customers and Orders tables:

INSERT INTO Customers
    (customerId,
     [name],
     [lastName],
     [address],
     bankAccount)
VALUES
    (2,
    'Mavin',
    'Pettitt',
    '2336 Cottonwood Lane',
    123456789);

INSERT INTO  Orders
    (orderId,
     product,
     productDescription,
     price,
     orderDate,
     productQuantity,
     customerId)
VALUES
    (2,
    'Play Station 5',
    'Sony Game Console',
    '549.95',
    '2022-07-18',
    4,
    2);

INSERT INTO  Orders
    (orderId,
     product,
     productDescription,
     price,
     orderDate,
     productQuantity,
     customerId)
VALUES
    (3,
    'Xbox series X',
    'Xbox Game Console',
    '499.99',
    '2022-01-21',
    2,
    2);

As with the other DML commands, several conditions can be specified in WHERE to filter the desired records:

DELETE FROM Orders
WHERE orderDate
BETWEEN '2022-01-01' AND '2022-07-31'
AND productDescription LIKE '%Game Console%';

This statement would also delete the records from the Orders table with orderId 2 and 3 respectively.

In the case of wanting to delete the value of a specific column/s, we must use the UPDATE statement, since DELETE deletes the entire record.

It is also possible to use DELETE in cross tables by some JOIN clause:

DELETE C FROM Customers C
INNER JOIN Orders O ON C.customerId = O.customerId
WHERE O.price > 10000;

In this case, as there are no products in the Orders table with a price greater than 10000 euros, no record has been deleted from the table.

DELETE statement in SQL

DELETE statement in SQL

See Also