DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Why Should Databases Go Natural?
  • SQL Interview Preparation Series: Mastering Questions and Answers Quickly
  • Keep Calm and Column Wise
  • Why SQL Isn’t the Right Fit for Graph Databases

Trending

  • Why Documentation Matters More Than You Think
  • Optimize Deployment Pipelines for Speed, Security and Seamless Automation
  • Artificial Intelligence, Real Consequences: Balancing Good vs Evil AI [Infographic]
  • Start Coding With Google Cloud Workstations
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL INSERT, UPDATE, DELETE — Oh My!

SQL INSERT, UPDATE, DELETE — Oh My!

In this article, learn about SQL INSERT, UPDATE, and DELETE statements and explore a case study.

By 
Rebecca McKeown user avatar
Rebecca McKeown
·
Feb. 07, 20 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
83.0K Views

Join the DZone community and get the full member experience.

Join For Free

SQL INSERT, UPDATE, and 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! INSERT, UPDATE, and DELETE are all functions in SQL that help you ensure your data is up-to-date and kept clear of unnecessary or outdated information.

INSERT, UPDATE, and 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, but also how to INSERT, UPDATE, and DELETE in SQL.

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:

Patient table



PatientID Surname FirstName DOB Sex Weight Height Vaccinated
15223 Smith Deniz 12/31/2018 F 21.4 29.2 Y
15224 Agarwal Arjun 08/29/2017 M 28.1 34.2 Y
15225 Adams Poppy 02/14/2015 F 34.0 39.2 N
15226 Johnson Tierra 08/15/2019 F 14.6 24.5 Y
15227 Khouri Mohammed 03/30/2014 M 41.5 44.1 Y
15228 Jones Ben 04/04/2011 M 70.1 52.2 Y
15229 Kowalczyk Alexandra 08/27/2019 F 15.2 23.9 Y

Sample table number two carries the different departments of the hospital and the groups and categories those departments fall under:

Department table



DepartmentID DepartmentName GroupName CategoryName
1 Cardiology Heart Center Clinical
2 Central ICU Emergency Clinical
3 Emergency Emergency Clinical
4 Communications Administration Operational
5 Oncology Internal Medicine Clinical
6 Neurology Internal Medicine Clinical
7 Human Resources Administration Operational
8 Pathology Service Technical
9 Radiology Service Technical
10 Pharmacy Service Technical
11 Executive Board Administration Operational
12 Urology Surgery Clinical
13 Hematology Internal Medicine Clinical
14 Montana Ward Ward Operational
15 Chicago Ward Ward Operational
16 Lincoln Ward Ward Operational
17 Yellowstone Ward Ward Operational
18 Brooklyn Ward Ward Operational

Sample table number three records patients’ visits over the years:

PatientAdmittance table



PatientID LastAdmitted LastDischarged
33 12/29/1952 01/05/1953
34 - -
35 08/01/2004 08/04/2004
36 07/28/2011 07/30/2011
37 05/27/1950 05/30/1950
38 - -
39 10/11/1970 10/20/1970

Armed with these sample tables, let’s get into the nitty-gritty of INSERT, UPDATE, and DELETE.

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 statement:

SQL
 




xxxxxxxxxx
1


 
1
INSERT INTO tableName
2
(column1, column2, …)
3
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:

SQL
 




xxxxxxxxxx
1


 
1
INSERT INTO dbo.Patient
2
            (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated)
3
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:

Table

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.

SQL
 




xxxxxxxxxx
1


 
1
INSERT INTO dbo.Patient
2
            (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated)
3
VALUES      ('Hitson', 'George','2019-11-19', 'M', 13.9, 22.5, 'No'),
4
VALUES      ('Hitson', 'Jenny','2019-11-19', 'F', 13.7, 22.3, 'No')



Tip! Use a transaction to test out your insert without committing and permanently altering your table. To do this, start with BEGIN TRANSACTION, and end the transaction with either COMMIT, if you want to keep the changes, or ROLLBACK, if you want to reverse what you have done. Below is how we’d add a transaction to the above example:

Begin Transaction

SQL
 




xxxxxxxxxx
1


 
1
BEGIN TRANSACTION
2
INSERT INTO dbo.Patient
3
            (Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated)
4
VALUES      ('Hitson', 'George','2019-11-19', 'M', 13.9, 22.5, 'No'),
5
            ('Hitson', 'Jenny','2019-11-19', 'F', 13.7, 22.3, 'No')
6
 
7
ROLLBACK
8
--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:

SQL
 




xxxxxxxxxx
1


 
1
INSERT INTO targetTable (column1, column2, …)
2
SELECT      (column1, column2, …)
3
FROM        sourceTable



The statement for our drug trial example would look like this:

SQL
 




xxxxxxxxxx
1
13


 
1
INSERT INTO [dbo].[DrugTrialAlpha]
2
           (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes)
3
SELECT
4
    p.Surname, p.FirstName, p.DOB, p.Sex, 'Drug', NULL
5
FROM dbo.Patient AS p
6
WHERE
7
    p.PatientID IN (15226, 15229, 15230)
8
UNION
9
SELECT
10
    p.Surname, p.FirstName, p.DOB, p.Sex, 'Placebo', NULL
11
FROM dbo.Patient AS p
12
WHERE
13
    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:

SQL
 




xxxxxxxxxx
1
18


 
1
WITH drugTrialPatients (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes)
2
AS (
3
    SELECT
4
        p.Surname, p.FirstName, p.DOB, p.Sex, 'Drug', NULL
5
    FROM dbo.Patient AS p
6
    WHERE
7
        p.PatientID IN (15226, 15229, 15230)
8
    UNION
9
    SELECT
10
        p.Surname, p.FirstName, p.DOB, p.Sex, 'Placebo', NULL
11
    FROM dbo.Patient AS p
12
    WHERE
13
        p.PatientID IN (15231)
14
    )
15
INSERT INTO [dbo].[DrugTrialAlpha]
16
           (Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes)
17
SELECT Surname, FirstName, DOB, Sex, DrugOrPlacebo, Notes
18
FROM drugTrialPatients



Table

Tip! If you’re using SELECT to add data from one table to another, it’s a good idea as an initial step to run the SELECT statement alone—just to make sure the right rows are returned and you’re happy with the result!

UPDATE Data Using SQL

The 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:

SQL
 




xxxxxxxxxx
1


 
1
UPDATE tableName
2
SET column1=value1, column2=value2,...
3
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.

Tip! Because a new column value can effect more than one row, make sure you’re happy with the extent of your update before committing! Just like INSERT, an SQL UPDATE can be written as a transaction, meaning you can either COMMIT or ROLLBACK the update depending on whether or not you’re happy with the changes.

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:

SQL
 




xxxxxxxxxx
1


 
1
UPDATE dbo.Department
2
   SET DepartmentName = 'Trauma and Emergency Surgery'
3
WHERE DepartmentID = 3
4
SELECT * FROM dbo.Department WHERE DepartmentName = 'Emergency'



Table

Here’s what the updated department name would look like in our table:

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.

SQL
 




xxxxxxxxxx
1


 
1
UPDATE dbo.Department
2
   SET GroupName = 'Room'
3
WHERE GroupName = 'Ward'



Which would change the table to look like this:

Table

DELETE Data Using SQL

baby onsie with code on it

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:

SQL
 




xxxxxxxxxx
1


 
1
DELETE tableName
2
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:

SQL
 




xxxxxxxxxx
1


 
1
DELETE FROM dbo.Department
2
WHERE DepartmentID = 16


table

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 statement:

SQL
 




xxxxxxxxxx
1


 
1
DELETE FROM dbo.PatientAdmittance 
2
WHERE LastDischarged < '1969-01-01'



The result? This is the table before…

Table

… and this is how it looks after we run that script:

Table

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:

SQL
 




x


 
1
SELECT * FROM dbo.PatientAdmittance 
2
WHERE LastDischarged < '1969-01-01'



Warning! If you use DELETE without adding a WHERE clause, you’ll delete every row out of your table.

Conclusion

Once you’ve mastered the SQL Basics, then SQL INSERT, UPDATE, and DELETE statements are your next step to database glory!


Database sql Relational database

Opinions expressed by DZone contributors are their own.

Related

  • Why Should Databases Go Natural?
  • SQL Interview Preparation Series: Mastering Questions and Answers Quickly
  • Keep Calm and Column Wise
  • Why SQL Isn’t the Right Fit for Graph Databases

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!