Need to understand SQL
DELETE? We’ll get you up to speed with a comprehensive How-To and useful examples!
DELETE statements—what are they all about? If you’re a data engineer, a database administrator, or even just your average data fanboy or girl, one day you’re going to find yourself with a database that becomes your “baby.” Charged with this special bundle of joy, you’re going to need to feed and change the little fella. Yes, we’re still talking about databases!
DELETE are all functions in SQL that help you ensure your data is up-to-date and kept clear of unnecessary or outdated information.
DELETE, as well as SELECT and MERGE, are known as Data Manipulation Language (DML) statements, which let SQL users view and manage data. While data is, of course, often updated by the application itself, it regularly requires manual entry or maintenance, and this demands not only a good understanding of SQL Basics (this article assumes you have this basic knowledge of SQL—check out our SQL Basics course and range of SQL articles if you haven’t already) but also how to
DELETE in SQL.
This article offers the basics of SQL
UPDATE, and SQL
DELETE, but if you want a more comprehensive tutorial, check out our How to INSERT, UPDATE, and DELETE Data in SQL course. It has everything you need to make DML statements child’s play.
Mickey Mouse Children’s Hospital – A Database Case Study
Let’s look at these SQL triplets one-by-one and understand how they work.
We’ll use a sample database to demonstrate each of the three. Keeping with the baby theme, let’s say you’re the data administrator at a children’s hospital, “Mickey Mouse Children’s Hospital,” to be precise. The hospital keeps records of all its little patients, including their age, weight, blood test results—you get the adorable picture.
Usually, the doctors enter and update this information into the hospital’s system each time they see their patients, and much of the inserting, updating, and deleting of records into the database is performed by the system. But often there are things that need manual fixing, and as the resident database administrator, this is your time to shine!
In the article below, we use three different tables to demonstrate the three different statements.
Sample table number one shows patient records and their basic information:
Sample table number two carries the different departments of the hospital and the groups and categories those departments fall under:
Sample table number three records patients’ visits over the years:
Armed with these sample tables, let’s get into the nitty-gritty of
INSERT Data Using SQL
Just like babies, there’s new data born every minute. With lots of savvy systems in place, often that data is added seamlessly to its intended databases. In many instances, though, you’ll find you need to add it manually, and that’s where the
INSERT statement in SQL comes in.
INSERT is your go-to for adding single or multiple rows to a table.
INSERT can also help with adding rows from one table into another table.
Let’s look at the basic structure of an SQL
INSERT INTO tableName (column1, column2, …) VALUES (value1, value2, …)
First, we need to identify the table we’re wanting to insert rows into. Next, we specify the columns we want to fill. Finally, we stipulate the values we need to add. This is the basic form of the
INSERT feature, and it’s pretty intuitive.
To give this structure some real-life application, here’s an example from our case study.
One of the doctors at Mickey Mouse Children’s Hospital has a newborn patient who has not yet been named. The doctor needs to enter the baby into the database immediately in order to access a drug trial for the sick child, but the system won’t allow the littlun’s file to be submitted without a name. The doctor wants the patient to be added using her patient number, with her name left blank until a later date. As the administrator, you’ll need to enter the patient manually into the database.
Here’s how the
INSERT statement would look:
INSERT INTO dbo.Patient (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated) VALUES (NULL, NULL,'2019-11-19', 'F', 14.0, 23.1, 'No')
As you can see, we’re not only giving the name of the table we need to insert into but also the names of the columns and values we want to add. We don’t specify the PatientID in the column list because as an identity column, this is automatically populated.
Because we want to keep the patient’s name blank for now, we write NULL for the name columns.
Once we run this command, it will create the following addition to the database:
Inserting Multiple Rows
To insert more than one row of data with just one statement, use parentheses and commas to specify the distinct new rows.
INSERT INTO dbo.Patient (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated) VALUES ('Hitson', 'George','2019-11-19', 'M', 13.9, 22.5, 'No'), VALUES ('Hitson', 'Jenny','2019-11-19', 'F', 13.7, 22.3, 'No')
BEGIN TRANSACTION INSERT INTO dbo.Patient (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated) VALUES ('Hitson', 'George','2019-11-19', 'M', 13.9, 22.5, 'No'), ('Hitson', 'Jenny','2019-11-19', 'F', 13.7, 22.3, 'No') ROLLBACK --COMMIT
Inserting from One Table to Another
Need to insert one or multiple rows of data from one table into another table? You can use the
INSERT INTO SELECT statement.
Let’s say several of our young patients are taking part in a new drug trial, and you’re setting up a new table to record their participation. The basic structure of this statement is:
INSERT INTO targetTable (column1, column2, …) SELECT (column1, column2, …) FROM sourceTable
The statement for our drug trial example would look like this:
INSERT INTO [dbo].[DrugTrialAlpha] (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes) SELECT p.Surname, p.FirstName, p.DOB, p.Sex, 'Drug', NULL FROM dbo.Patient AS p WHERE p.PatientID IN (15226, 15229, 15230) UNION SELECT p.Surname, p.FirstName, p.DOB, p.Sex, 'Placebo', NULL FROM dbo.Patient AS p WHERE p.PatientID IN (15231)
You could also write the example as a Common Table Expression (CTE). Among other things, CTE can be used to create a temporary result set which can be reused during the query. Here’s how the same command written as a CTE would look:
WITH drugTrialPatients (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes) AS ( SELECT p.Surname, p.FirstName, p.DOB, p.Sex, 'Drug', NULL FROM dbo.Patient AS p WHERE p.PatientID IN (15226, 15229, 15230) UNION SELECT p.Surname, p.FirstName, p.DOB, p.Sex, 'Placebo', NULL FROM dbo.Patient AS p WHERE p.PatientID IN (15231) ) INSERT INTO [dbo].[DrugTrialAlpha] (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes) SELECT Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes FROM drugTrialPatients
UPDATE Data Using SQL
UPDATE statement is another common task used by data engineers and database administrators. Use it to change a column value for one or multiple rows.
Here’s how an SQL
UPDATE statement is generally structured:
UPDATE tableName SET column1=value1, column2=value2,... WHERE filterColumn=filterValue
Just like with the SQL
INSERT statement, first, we need to identify the table we’re wanting to update. Then we use the
SET clause which details the columns we want to update. Finally, we use the
WHERE clause to pinpoint which rows we want to include in the update.
Here’s an example using our case study. Our Emergency department is rebranding to the “Trauma and Emergency Surgery” department, so its name needs changing in the database. To update this name, you could execute this statement:
UPDATE dbo.Department SET DepartmentName = 'Trauma and Emergency Surgery' WHERE DepartmentID = 3 SELECT * FROM dbo.Department WHERE DepartmentName = 'Emergency'
Here’s what the updated department name would look like in our table:
When using the SQL
UPDATE statement, make sure your
WHERE clause specifies precisely which rows you want to update. When in doubt, write the statement as a transaction, which you can then roll back if you’re not happy with the change—no harm done!
To check you’re selecting the right data to update in the first place, you can do a test by using
SELECT to make sure you’re targeting the right rows.
Update Multiple Rows
If you need to update multiple rows of data, it’s easy with the
UPDATE statement. The
WHERE clause is your friend here.
Imagine you want to recategorize all the inpatient wards from “Ward” to “Room” under the group column.
UPDATE dbo.Department SET GroupName = 'Room' WHERE GroupName = 'Ward'
Which would change the table to look like this:
DELETE Data Using SQL
DELETE is the diaper change of the SQL world. Is there something you don’t want in there? Delete it!
DELETE removes one or multiple rows from a table, but be careful! You need to make sure you know what you’re deleting before you go ahead and commit to the statement!
Here’s what your average SQL
DELETE statement looks like:
DELETE tableName WHERE filterColumn=filterValue;
It’s just two parts: specifying the table and specifying
WHERE—which rows you want to delete.
If you know the primary key of a row you want to delete, your job is simple. Do you want to delete the Lincoln Ward from your table? It’s just:
DELETE FROM dbo.Department WHERE DepartmentID = 16
Deleting Multiple Rows
Back we go to Mickey Mouse Children’s Hospital. Let’s say the hospital’s data manager wants to delete all patient records of those who haven’t visited the hospital since 1969. Here’s how you could write that
DELETE FROM dbo.PatientAdmittance WHERE LastDischarged < '1969-01-01'
The result? This is the table before…
… and this is how it looks after we run that script:
The best thing to do before running this
DELETE statement, however, would be to test the result using
SELECT. This will return all the rows you were about to delete, so you can check first whether you’re removing the right rows!
To run this test, you’d type:
SELECT * FROM dbo.PatientAdmittance WHERE LastDischarged < '1969-01-01'
Once you’ve mastered the SQL Basics, then SQL
DELETE statements are your next step to database glory! If this article has whet your appetite to learn more, check out our beginner-friendly course, How to INSERT, UPDATE, and DELETE Data in SQL. Not only will it go into this foundational information in more detail, but it will also teach you:
- How to deal with auto-filling values and how to combine SQL data manipulation commands with
- How to combine SQL DML commands with
- How to combine SQL DML commands with
- How to modify data using default values of columns.
- How to perform CRUD operations—the most important of the DML operations.
With 52 interactive exercises, lifetime access to the course, a certificate of completion, and a 30-day satisfaction money-back guarantee, there’s little to lose by giving it a go!
So, what are you waiting for?
INSERT some new knowledge into your life now,
UPDATE your SQL education, and
DELETE all those excuses! We’ll see you around the databases.