SQL UPDATE: A Guide

How to Use the SQL UPDATE Statement

One of the core features of databases is that stored values can be updated. You can change the values of any record in a database at any time. Values inside an SQL database are changed using the SQL UPDATE statement.

In this guide, we’re going to discuss what the SQL UPDATE statement is and how you can use it. We’ll walk through examples of this statement in action to help you understand how it works.

What is SQL UPDATE?

The UPDATE statement updates existing data in an SQL table.

It can be used to modify all records in a database or records that meet a certain condition or set of conditions. Here is the structure of a typical SQL UPDATE query:

UPDATE table
SET column1 = value1, column2 = value2…
WHERE condition(s);

“table” refers to the name of the table whose records you want to update.

The statements after the SET keyword are the names of the columns you want to update, followed by an equal sign, and then the value you want to assign to that column. To update multiple values, you need to separate each statement after SET with a comma.

Let’s walk through an example of the SQL UPDATE statement. We have a table called employees which has the following values:

We created this database in our tutorial on SQL INSERT. We’re going to update a few records inside this database.

SQL UPDATE in Action

Lisa Ingles has just married. Her surname has changed to Lisa Nelson. We can use an UPDATE statement to change her name in the employees table:

UPDATE employees
SET name = 'Lisa Nelson'
WHERE id = 2;

This command changes a single column using the SET clause: name. Let’s select her record from the database to verify that the changes have been made successfully:

Lisa’s surname has been changed.

Update More Than One Record

You can update multiple existing records in a table using a single UPDATE statement. Usually, a WHERE statement is used when updating multiple columns to ensure that only certain records are updated.

The executive team has decided that all Sales Associates are going to be renamed to Sales Representatives. To make this change, we’ll need to write an UPDATE command:

UPDATE employees
SET title = 'Sales Representative'
WHERE title = 'Sales Associate';

This command changes the value of “title” to “Sales Representative” for all Sales Associates. Let’s check to see if our change has been made:

Thomas Carlton and Lisa Nelson’s titles have been changed. Victoria’s title remains the same because she is a Sales Director.

Update All Records

By default, the UPDATE statement will amend all records in a database. You need to specify a WHERE statement to override this behavior.

Every employee at our business is receiving a 5% pay increase. To make this change, we’re going to use an UPDATE statement:

UPDATE employees
SET salary = salary * 1.05;

This command amends the value of “salary” in every record in the database. We have used the multiplication operator (*) to increase every employee’s salary by 1.05. This is equal to a 5% salary increase. Let’s check our our database:

The salaries of all our employees have been successfully amended.

Conclusion

The UPDATE statement amends one or more records in a database. It is usually used with a WHERE statement to select exactly what records should be amended.

If you’re looking for a challenge, write commands which:

• Increase the salaries of all Sales Representatives by 2%.
• Changes Lisa’s title to Senior Sales Representative.
• Changes the hired date of Victoria Carlisle to 04-03-2017.

Now you’re ready to start using the UPDATE statement like an SQL pro!