SQL Intermediate 5: UPDATE and DELETE Data

SQL

Vanessa Gottardi

5/10/20235 min read

Welcome to our SQL Intermediate series, where we take your SQL skills to the next level! In this post, we'll explore two fundamental operations: updating and deleting data in SQL. These powerful tools allow you to maintain data accuracy, make necessary changes to records, and ensure your database stays relevant and up-to-date.

Hello everyone and welcome to our SQL Intermediate 5: UPDATING and DELETING Data.

At the end of this post, you can find links for SQL Basics and SQL Intermediate series in case you want to catch up with previous exercises.

Structured Query Language (SQL) is a powerful tool used to manage, manipulate, and query data in relational databases. In this blog post, we will explore two fundamental operations: updating and deleting data in SQL.

These operations are essential for maintaining data accuracy and integrity, allowing database administrators and developers to ensure that information is always up-to-date and relevant. Let's dive into the details of updating and deleting data in SQL.

UPDATE Data in SQL:

Updating data in SQL involves modifying existing records in a table. The most commonly used SQL statement for this purpose is the UPDATE statement.

The basic syntax of an UPDATE statement is:

UPDATE: This keyword indicates that you want to modify existing records in a table.

table_name: The name of the table from which you want to update data. Replace "table_name" with the actual name of the table you want to work with.

SET: This keyword is used to specify the columns that you want to update and the new values you want to assign to them.

column1 = value1, column2 = value2: In this part of the syntax, you specify the columns you want to update, followed by the new values you want to set for each of those columns. Separate multiple column-value pairs with commas.

WHERE condition: The optional WHERE clause allows you to specify which rows you want to update based on a condition. If you omit the WHERE clause, the UPDATE statement will update all rows in the specified table.

Difference Between INSERT INTO and UPDATE Data into a Table in SQL:

INSERT INTO creates a new row in a table.
UPDATE alters a pre-existing row while the leading specifies what rows we want to remove from our table.

Let’s go for our first example using our ‘EmployeeDemographics’ table.

NOTE: If you don’t have the table yet and want to follow along with this exercise:

SQL Basics 1: How to Create Table and Add Data in SSMS

Row 11, Sarah does not have an EmployeeID, LastName or Age. After HR talked to Sarah, they were able to get this information and we want to update it in our system.

UPDATE EmployeeDemographics

SET EmployeeID = 1011, LastName = ‘Fontes’, Age = 52

WHERE FirstName = ‘Sarah’ AND Gender = ‘Female’" -> Execute

Boom! We have Sarah’s information updated.

Just for fun, let’s update EmployeeID 1010 information as well.

UPDATE EmployeeDemographics

SET FirstName = ‘Maria’, LastName = ‘Santos’, Gender = ‘Female’

WHERE EmployeeID = 1010” -> Execute

Easily updated 😊

DELETE Data in SQL:

Deleting data in SQL involves removing records from a table. The DELETE statement is used for this purpose.

The basic syntax of a DELETE statement is as follows:

DELETE: This keyword indicates that you want to delete rows from a table.

FROM table_name: The name of the table from which you want to delete rows. Replace "table_name" with the actual name of the table you want to work with.

WHERE condition: The optional WHERE clause allows you to specify which rows you want to delete based on a condition. If you omit the WHERE clause, the DELETE statement will remove all rows from the specified table.

Example 1:

You just found out that EmployeeID 1010, Maria, is not an employee and we want to delete the entire row.

“DELETE FROM EmployeeDemographics WHERE EmployeeID = 1010” -> Execute

EmployeeID 1010 is now deleted.

NOTE: We need to be very careful when using DELETE FROM statement because, once you run it, you CANNOT reverse and get the data back.

If we run the query:

DELETE FROM EmployeeDemographics -> Execute

The entire table will be deleted and we could NOT get that data back.

TRICK: What we can do to make sure we don’t delete data we actually want is to, first SELECT the data to visualize it, then use the DELETE FROM statement.

Let me show you...

You just realized that Sarah’s information is not correct either and ww want to delete it. First, we check the data:

SELECT * FROM EmployeeDemographics WHERE EmployeeID = 1011 -> Execute

After making sure we selected the right data, now we can delete it by replacing “SELECT *” with “DELETE”.

Sarah’s data is deleted in a safer way. By using SELECT and checking the information before deleting, can be a safeguard against accidently deleting data that you do NOT want to delete.

Important Considerations When Using UPDATE and DELETE:

Be cautious with the WHERE clause:

When updating or deleting data, ensure that you have a well-defined and accurate WHERE clause. An incorrect or missing WHERE clause can lead to unintended updates or deletions of data.

Backup data:

Before performing any updates or deletions, it's good practice to create a backup of the data. This way, you can restore the data if something goes wrong during the process.

Transactions:

Use transactions when making multiple updates or deletions simultaneously. Transactions ensure that all changes are committed together or none at all, maintaining data consistency.

Indexes and Performance:

Keep in mind that updating or deleting large amounts of data can impact database performance. Properly indexed tables can significantly improve the efficiency of these operations.

Updating and deleting data in SQL are essential operations for maintaining accurate and relevant information in a database. The UPDATE statement allows you to modify existing records, while the DELETE statement enables the removal of unwanted data. By understanding the syntax and considering the important considerations mentioned in this blog post, you can confidently manage data in your SQL database and ensure its integrity and accuracy.

If you like this content and want to learn more, you might also read:

SQL Basics 1: How to Create Table and Add Data in SSMS

SQL Basics 2: SELECT + FROM Statements

SQL Basics 3: WHERE Statement

SQL Basics 4: GROUP BY and ORDER BY Statements

SQL Intermediate 1: INNER and OUTER Joins

SQL Intermediate 2: UNION Operator:

SQL Intermediate 3: CASE Statement

SQL Intermediate 4: HAVING Clause

SQL Intermediate 5: UPDATE and DELETE Data

SQL Intermediate 6: AS - Aliasing

SQL Intermediate 7: PARTITION BY Clause