What Is the UPDATE Statement in SQL?

The SQL UPDATE statement is used to update existing data in your database. This article will explain its syntax and show you clear examples of the UPDATE statement in action.

Effectively updating existing data in a database is required for anyone using SQL; data is only useful if it is kept up-to-date and relevant.

In this article, we’ll examine the syntax of the UPDATE statement in great detail. Then we’ll dive into detailed step-by-step examples that will drive home the rules, potential uses, and limitations associated with the SQL UPDATE statement.

What Is the UPDATE Statement?

In SQL, the UPDATE statement is used to modify or update existing records in a table. You can use it to update everything all at once, or you can specify a subset of records to modify using the WHERE clause.

The UPDATE statement is considered a SQL data manipulation command. The other commands are INSERT and DELETE, which you can learn about in the How to INSERT, UPDATE, or DELETE Data in SQL course from LearnSQL.com.

Let’s take a look at the SQL UPDATE syntax.

SQL UPDATE Syntax

The syntax of the UPDATE statement is as follows:

UPDATE <table>
SET <column1> = <value1>,
        <column2> = <value2>,
        …
[WHERE <conditions>]

Let’s break down this syntax.

The table name follows directly after the UPDATE statement itself: UPDATE <table>.

You can specify the columns you would like to update using the SET keyword. Note that when setting the values of your columns, you must use the correct data type. Let’s look at an example SQL query that demonstrates this.

UPDATE employee
SET 	id = 1,
   	first_name = 'John'

When setting the id column value, no quotation marks are needed. However, when setting the first_name column, you must pass in a string value, i.e. by enclosing the text in single quotation marks.

The last part of the syntax is the optional inclusion of the WHERE clause. Although optional, I typically always include WHERE in my UPDATE statements. Failure to include a WHERE clause to specify the exact record to modify will result in you updating every single value in that column.

Let’s look at an example that demonstrates this. We have two queries that are almost identical; the difference is that one uses WHERE and one does not:

UPDATE employee
	SET first_name = 'John'

UPDATE employee
	SET first_name = 'John'
	WHERE id = 1

Query 1 will update every employee’s first name to John – you probably don’t want this to happen!

Query 2 updates one employee’s name to John: the one where that employee’s id is equal to 1. That’s probably what you’re hoping to accomplish.

It’s a good practice to use SELECT to view the records before you go ahead and update them. For example, you could write this query:

SELECT *
FROM employee
WHERE id = 1

If the record returned is indeed the record you would like to modify, you can use the same WHERE clause for your UPDATE statement. This ensures you do not accidentally modify any other records.

And that concludes our look at the SQL UPDATE syntax. Now it’s time to look at some practical examples that will demonstrate some different uses of the UPDATE statement. We’ll also touch on some permission issues you might face.

SQL UPDATE Permissions

Before our next example, let’s talk about permissions. It’s important to note that, depending on what SQL dialect you’re using, you may have to change permissions so you can modify records directly with the UPDATE statement.

For example, in MySQL you will encounter the message: “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect”.

To resolve this, you have two options.

1. Change your settings:
   1. Go to Edit →
   2. Click the SQL Editor tab and uncheck the Safe Updates check box
   3. Click Query → Reconnect to Server.
   4. Execute your SQL query.
2. Run the following query:
   

   SET SQL_SAFE_UPDATES = 0;

After completing either of these options, you should be able to use UPDATE to modify table data.

Using UPDATE on Columns

Imagine we have a table containing the name and ages of employees at a company. The database table is supposed to be automatically updated by the connected HR system – but on reviewing the table, you can see that some of the ages are out of date. You decide the best course of action is to use the SQL UPDATE statement to manually update the ages of the affected employees.

First of all, let’s look at the employees table:

You can see each row contains a unique identifier for the employee, along with their first_name, last_name, and age.

The employees we need to update are Paul Johnson and Jacob Ward.

Let’s get started with updating the age of these employees:

UPDATE employees
SET age = 34
WHERE employee_id = 3;

Ok, this query should do it. The employee_id value of 3 corresponds to Paul Johnson. There is only one occurrence of 3 in the employee_id column, so this query will not update any other records. Execute the query and use the following SELECT query to view the results:

SELECT employee_id, first_name, last_name, age
FROM employees
WHERE employee_id = 3;

The age has been successfully updated!

For our next employee, let’s update their age by using their first_name and last_name in the WHERE clause. As long as there are no two employees with the same first and last names, this will work as we want it to:

UPDATE employees
SET age = 41
WHERE first_name = 'Jacob' AND last_name = 'Ward'

That should do it! Execute the query then use this SELECT statement to view the result:

SELECT employee_id, first_name, last_name, age
FROM employees
WHERE first_name = 'Jacob' AND last_name = 'Ward'

That’s exactly what we wanted! For more examples like this, check out this article about how to use subqueries with INSERT, UPDATE, and DELETE.

Next, let’s look at a more advanced example; we will update one table using data from another table.

Updating a Table with Data from Another Table

Imagine a scenario where someone was updating records in the employees table and made a mistake! They accidentally set the first 5 rows to have the first name ‘John’. The employees table now looks like this:

How do we fix this? Thankfully, we have a backup table that was not affected by the developer’s mistake. This table is called employees_backup and looks like this:

Let’s write a query that updates the incorrect values in employees with the correct values from the backup table. When you update multiple rows, it usually involves a lot of manual work. This query is designed to cut down on that repetitive work:

SET first_name = (SELECT first_name
                  FROM employees_backup
                  WHERE employees_backup.last_name = employees.last_name)
WHERE employee_id < 6;

Let’s break this down. You can see that the only column we want to modify is first_name, but only where the employee_id of that record is less than 6. This is because the developer’s mistake only affected the first 5 records of the table.

We then select the values from the first_name column of the employees_backup table, matching the employees on their last name. This is how we ensure the correct first name is given to each employee.

This is a useful scenario to keep in mind; something similar may occur when you’re working with databases. Having a solid grasp of the SQL UPDATE statement allows you to correct mistakes with ease.

Achieve Complete Control Over Your Data with SQL UPDATE

That concludes our article on SQL UPDATE. We’ve covered the syntax and the permissions needed to use the UPDATE statement. We’ve examined some practical examples – simple cases as well as more advanced scenarios like using data from another table.

We also mentioned how INSERT is one of the ways to alter the data inside your tables. The other commands that change data are INSERT and DELETE. Learn more about them in this blog post about INSERT, UPDATE, and DELETE.