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
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:
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
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
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
Here are the scripts to re-insert the records from the
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
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.