Delete Statement
Deleting data from tables in a database is an important aspect of managing data. Knowing how to correctly delete records using SQL commands can save time and energy when dealing with large amounts of information. In this article, we will discuss the basics of the DELETE command in T-SQL, including syntax and examples. We will also cover topics such as deleting multiple rows at once and conditional deleting. By the end of this article, you will have developed a strong understanding of how to use the DELETE command for your future database projects.
SQL DELETE Statement Is Permanent
Deleting in SQL Server's T-SQL is a permanent process - there's no such thing as an "are you sure" prompt. In other words, when it comes to deleting data from your database, the old saying "what's done is done" has never been more applicable. Without a way to undo, it is essential to know and understand the syntax of and execute the DELETE command.
The basic syntax for deleting records from a table record using T-SQL is as follows: "DELETE FROM table_name WHERE some condition". The first part, "DELETE FROM", specifies that you want to only delete records from all the records from the specified table. The second part, "
Here's an example T-SQL CREATE TABLE statement that you can use to create and query a table called students with columns id, name, age, and grade:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade INT
);To insert the following 15 records into the students table with different names than Bob, Jane, and Mike, you can use the following INSERT statements:
INSERT INTO students (id, name, age, grade) VALUES
(1, 'Alice', 18, 85),
(2, 'Tom', 19, 92),
(3, 'Emily', 20, 78),
(4, 'Jack', 19, 87),
(5, 'Lily', 18, 90),
(6, 'William', 19, 83),
(7, 'Sophia', 20, 95),
(8, 'Ethan', 18, 89),
(9, 'Olivia', 19, 91),
(10, 'Noah', 18, 86),
(11, 'Isabella', 20, 88),
(12, 'Jacob', 19, 84),
(13, 'Ava', 18, 93),
(14, 'Lucas', 20, 82),
(15, 'Mia', 19, 94);
This will insert 15 rows into the students table with different names, ages, and grades. You can then run DELETE statements to test deleting one row or more rows from each record in the students table.
Here are examples of a few examples of different DELETE statements you can use to delete rows from a record in the students table we created earlier:
Delete all rows from the table:
DELETE FROM students;This will delete all rows from the students table.
Delete With Where Clause Based On The ID Column:
DELETE FROM students WHERE id = 3;
his will delete the row with id equal to 3 from the students table.
Delete rows based on a condition using the LIKE operator:
DELETE FROM students WHERE name LIKE 'J%';
This will delete all rows from the students table where the name column starts with the letter "J".
Delete rows based on a condition using the BETWEEN operator:
DELETE FROM students WHERE age BETWEEN 18 AND 19;
This will delete all rows from the students table where the age column is between 18 and 19.
Delete rows based on a condition using the IN operator:
DELETE FROM students WHERE grade IN (78, 83, 90);This will delete all rows from the table based the students table where the grade column is equal to 78, 83, or 90.
Using TOP to limit the number of rows deleted
here's an example of using the TOP clause to limit and count the number of value of rows deleted with a DELETE statement:
DELETE TOP(5) FROM students;
This will only delete a row from the top two tables and 5 rows from the students table. You can modify the number in the TOP clause to delete multiple rows or a different number of rows.
If you want to delete a specific number of one or more rows, based on a condition, you can combine multiple conditions of the TOP clause with the WHERE clause like this:
DELETE TOP(3) FROM students WHERE age >= 20;This will also delete existing records in the top 3 rows from the students table where the value in the age column is greater than or equal to 20.
Note that the TOP clause only applies to the count and number of rows that are deleted, not to the order in which they are deleted. If you need to specify a specific order for the rows to be deleted, you should use an ORDER BY clause before the TOP clause.
Using joins and subqueries to data in one table to delete rows in another table
Let's add a new table called enrollments to our previous example above. This table will contain information about which students are enrolled in which courses. Here's an example CREATE TABLE statement for the enrollments table:
CREATE TABLE enrollments (
id INT PRIMARY KEY,
student_id INT,
course VARCHAR(50),
year INT,
semester VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES students(id)
);This table has a foreign key constraint that references the id column in the students table.
Now, let's populate this table with some sample data:
INSERT INTO enrollments (id, student_id, course, year, semester) VALUES
(1, 1, 'Mathematics', 2022, 'Fall'),
(2, 2, 'English', 2022, 'Fall'),
(3, 3, 'History', 2022, 'Fall'),
(4, 4, 'Mathematics', 2022, 'Fall'),
(5, 5, 'Biology', 2022, 'Fall'),
(6, 6, 'Chemistry', 2022, 'Fall'),
(7, 7, 'Mathematics', 2022, 'Fall'),
(8, 8, 'English', 2022, 'Fall'),
(9, 9, 'History', 2022, 'Fall'),
(10, 10, 'Mathematics', 2022, 'Fall'),
(11, 11, 'Biology', 2022, 'Fall'),
(12, 12, 'Chemistry', 2022, 'Fall'),
(13, 13, 'Mathematics', 2022, 'Fall'),
(14, 14, 'English', 2022, 'Fall'),
(15, 15, 'History', 2022, 'Fall');This inserts 15 rows into the enrollments table, with each row specifying which student is enrolled in which course for the fall 2022 semester.
Now, let's say we want to delete existing records of all the students from the students table who are not enrolled in any courses in the fall 2022 semester. We can use a subquery to get a list of all the id values in the students table that are not present in the student_id column of the enrollments table for the fall 2022 semester. We can then use a DELETE statement with a JOIN to delete all the rows from the students table with those id values.
Here's the example Of A Delete With A Join:
DELETE students
FROM students
LEFT JOIN (
SELECT DISTINCT student_id
FROM enrollments
WHERE year = 2022 AND semester = 'Fall'
) enrolled_students
ON students.id = enrolled_students.student_id
WHERE enrolled_students.student_id IS NULL;This code first selects all the distinct student_id values from the enrollments table for the fall 2022 semester using a subquery. It then performs a LEFT JOIN between the students table and this subquery on the id and student_id columns, respectively. The LEFT JOIN ensures that all the corresponding rows from the students table are included in the result set, even if there is no matching row in the subquery. Finally, the WHERE clause selects only the empty rows in a table where there is no matching row in the subquery, i.e., all the records of students who are not enrolled in any courses in the fall 2022 semester. These rows are then deleted from the students table using
Using DELETE with the OUTPUT clause
The OUTPUT clause allows you to capture the results of a DELETE statement, including the rows that were deleted following delete statement. Here's an example of how to use the OUTPUT clause with and following a DELETE statement:
DELETE FROM students
OUTPUT deleted.*
WHERE age > 25;This DELETE statement deletes all the rows from the students table where the age is greater than 25. The OUTPUT clause captures the deleted rows and returns them as a result set.
You can specify which columns you want to return by replacing deleted.* with a comma-separated list of column names. For example, if you only want to return the id and name columns of the deleted rows in a table, you can write:
DELETE FROM students
OUTPUT deleted.id, deleted.name
WHERE age > 25;You can also capture the deleted database rows in a table variable or a temporary table by specifying the name of the table after the OUTPUT keyword. For example:
DECLARE @deleted_students TABLE (
id INT,
name VARCHAR(50),
age INT
);
DELETE FROM students
OUTPUT deleted.id, deleted.name, deleted.age INTO @deleted_students
WHERE age > 25;
This code creates a table variable @deleted_students with columns for the id, name, and age columns of the students table. The DELETE statement deletes all the rows from a table where the age is greater than more than one condition of 25, and inserts the deleted rows into the @deleted_students table.