SQL Delete Rows from Table
DELETE in SQL is used to remove row(s) from a table. There are different ways DELETE can be used to do this. An example will first be shown demonstrating some of these methods. A breakdown will follow with some additional details on the pieces in the example.
/* 1) Create the tables and add rows */ CREATE TABLE CustomerTest ( Id INT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY (Id) ); INSERT INTO CustomerTest(Id, FirstName, LastName) VALUES (1, 'Susan', 'Jones'), (2, 'Kyle', 'Smith'), (3, 'Jacob', 'Henry'), (4, 'Sally', 'Wells'), (5, 'Bob', 'Wiles'), (6, 'Steve', 'Henry'); /* 2) Conditional DELETE without JOIN */ DELETE FROM CustomerTest WHERE Id = 1; /* 3) Conditional DELETE with JOIN */ WITH RowsToDelete (RowId) AS ( SELECT 2 UNION SELECT 3 ) -- Note that you may need to disable safe update -- in MySQL for this delete to work. DELETE c FROM CustomerTest c JOIN RowsToDelete r on r.RowId = c.Id; /* 4) Results */ SELECT * FROM CustomerTest; /* 5) Remove table CustomerTest */ DROP TABLE CustomerTest;
1) Create the tables and add rows
In this step, we’re creating a new “CustomerTest” table that will be used to track customer information (just the customers’ name pieces in this example). All columns in this table must be given a value in the INSERT, which is enforced via the NOT NULL and PRIMARY KEY constraints.
For the Id, we’re just using an incrementing number (i.e., 1, 2, 3, …). This is manually being added per row in the INSERT. One side note here is that the “Id” column could have an additional attribute added to it so that it would be an auto-incrementing number, which would let us add rows without needing to specify it manually. For SQL Server this would be IDENTITY(1, 1) and in MySQL AUTO_INCREMENT.
2) Conditional DELETE without JOIN
Here, a single row is being removed (row with Id of 1) using a conditional WHERE clause. After this statement runs, the “Susan Jones” row will no longer be in the table.
If the WHERE clause was removed from the statement, then all rows in the table would be deleted. Be careful of this in real-world usage.
3) Conditional DELETE with JOIN
This part shows how DELETE can be performed using a query with multiple tables. To do this, a CTE is being created as an example of another table.
The specific rows to delete by Id are being added to the CTE. In a real-world use case, something similar might be used. Or, a more complicated query using several tables in the JOIN might be needed depending on the context.
Since there is a JOIN from the RowsToDelete CTE to the CustomerTest table, only the rows with those Ids will be removed. After this statement runs, rows with Ids of 2 (Kyle Smith) and 3 (Jacob Henry) will be deleted from the table.
There’s nothing too special about this piece. The results are simply being retrieved. Only rows 4, 5, and 6 exist in the table at this point given the prior deletes.
5) Remove table CustomerTest
The last statement is fully removing the CustomerTest table using DROP. This differs from a DELETE without a condition, as explained in 2), in that a DELETE without a condition will remove all rows but the table will still exist. On the other hand, DROP removes the table entirely.
While the example above goes over some of the most commonly used methods for DELETE, there are still some additional ways or uses cases in which DELETE may be used. For SQL Server, a good article on this can be found at DELETE (Transact-SQL) - SQL Server | Microsoft Docs