SQL Update Statement Example

This is an article on SQL using the Update statement.

1. Introduction

We will look at one of the data manipulation language (DML) constructs or statements called Update statement using the MySQL RDBMS.

2. What is Data Manipulation Language?

In SQL, Data Manipulation language consists of SQL data-change statements that modify data but not the underlying schema or database objects. The commands included in the data manipulation language are:

• Insert – add new records/tuples in table or tables.
• Update – alter records/tuples already present.
• Delete – remove records/tuples.

Unlike Data Definition statements, DML statements are not auto-commit transactions by default. Hence, until we specifically “commit” the transaction, the changes done by these statements can be undone. These commands correspond to the “CRUD” (Create-retrieve-update-delete) statements which are used in most applications.

2.1 What is a Transaction?

A transaction simply put is a unit of work, which is performed in the order in a database. Transactions are used to propagate or reverse one or more changes done to a database. Transactions are used for DML statements and are extremely important to ensure data integrity. More on transactions in general here.

3. Update Syntax

The basic syntax for an update statement is as follows:

UPDATE table_name
SET column_name = value1, column2 = value2, ...
WHERE condition;

In the syntax above, the condition is particularly important. If the condition is not present i.e. no “where” clause is mentioned then all the records of the table will be updated.

4. Update statement in MySQL

According to MySQL, the update statement syntax is as follows

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

The multiple-table syntax is:

 UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

The “LOW_PRIORITY” and “IGNORE” are the two modifiers that are available with the Update statement in MySQL.

• LOW_PRIORITY = This modifier makes the update statement delay execution until no other connection is reading data from the table. This is used by table-level locking storage engines such as MyISAM, MERGE, and MEMORY.
• IGNORE = This modifier enables the update statement to ignore errors during the update and continues with the rest. The errored-out rows are not updated. This is especially useful for large update scripts.

4.1 Setup for examples

Forgoing through the examples related to Update, we will consider the database called “TestDB”. It has 4 tables: employees, departments, employee_dept, and dept_locations. The records currently present in it are as follows:

For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here. Set the Workbench to “Auto-commit Transactions”.

5. UPDATE ..SET Examples

Using the setup above we will look at various options and syntaxes used with the Update statements.

5.1 Update Single record from a table.

If we observe the record from emp_id=27 does not have a last_name which is incorrect. To update the record, we use the syntax

UPDATE EMPLOYEES
SET LAST_NAME = 'Mirkwood'
WHERE EMP_ID=27;

Here is we do not use the “where emp_id=27” condition, then all the records in the employees table will be updated.

5.2 Update multiple column values from a table

We can also update multiple columns from a table at once. To update the record(s), we use the syntax:

UPDATE EMPLOYEES
SET 
LAST_NAME = 'Firebeams',
BIRTH_DATE = '2785-02-01'
WHERE LAST_NAME IS NULL;

To be able to execute the update statement we need to unselect the option: ”Safe Updates(rejects UPDATEs and DELTEs with no restrictions)”. This option is present under Edit -> Preferences -> SQL Editor.

The update statement above will update 2 records, one for emp_id = 39 and emp_id = 40 with the same values for the Last_name and the birth_Date field.

5.3 Using the Update statement with Replace

We can use the replace statement with the update statement to update a particular string with another. This could affect multiple rows or a single row. The Query is:

UPDATE DEPARTMENTS
SET DEPT_NAME = REPLACE(dept_name,'Rnd','Research and Development')
WHERE DEPT_ID = 5;

The output is as follows

5.4 Update using the select statement as a subquery

We can select multiple records in a single update statement using the select subquery.

UPDATE EMPLOYEES
SET 
EMAIL = 'support@abc.com'
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_DEPT WHERE DEPT_ID = 2);

This would select all the records for department =2 and update them with a generic email i.e. support@abc.com.

5.5 Using the select statement in the set clause

Consider that we have to change the department id from its current value to the value of the department at the “Ravenhill” location for a specific record. We can do so by using the select statement in the set clause. The Query is as follows

UPDATE EMPLOYEE_DEPT
SET
DEPT_ID = (SELECT DEPT_ID FROM DEPT_LOCATIONS WHERE LOCATION_NAME = 'Ravenhill')
WHERE RECORD_ID=15;

The output of the same is as follows

5.6 Update clause with multiple tables: INNER JOIN

To consider this scenario we will expand the employee’s table to include 2 new columns ratings and salary. We will also have a new table called ratings which has a rating and a percentage. Based on the rating each employee gets, their salary is updated. This is applicable only to the Support department. For all other departments, all employees get a standard 40% hike in salary. To do so, we first updated the employee’s table with random ratings and salary.

The query for employees not in department = 2

update employees
set rating = 0,
    salary = FLOOR (50000 + RAND() * 130000)
where emp_id in (select emp_id from employee_dept where dept_id  2);

For employees in department = 2

update employees
set rating = FLOOR( 1 + RAND( ) * 5 ),
    salary = FLOOR (50000 + RAND() * 130000)
where emp_id in (select emp_id from employee_dept where dept_id =2);

Query to update the salaries according to the percentage as per the rating is as follows

UPDATE EMPLOYEES
 INNER JOIN
RATINGS ON EMPLOYEES.RATING = RATINGS.RATING_ID 
SET 
 SALARY = ROUND(SALARY + SALARY * PERCENTAGE ,0)
WHERE RATING  0;

For example, the salary for emp_id = 23 changes from 144100 to 172920 which is 80% more according to the rating which is 5.

5.7 SQL Update clause with multiple tables: LEFT JOIN

For the departments other than 2, the rating is set to 0 and we need to apply a standard 40% hike in salary. The Query to do so is

UPDATE EMPLOYEES
        LEFT JOIN
    RATINGS ON EMPLOYEES.RATING = RATINGS.RATING_ID 
SET 
    SALARY = ROUND(SALARY + SALARY * 0.40 ,0)
WHERE RATING = 0;

For example, the salary for emp_id = 1 changes from 95382 to 133535.

6. Summary

In the article, we saw syntaxes related to the UPDATE … SET statement in MySQL. The complete documentation of the UPDATE … SET Clause is available here.

7. Download the Source Code

This was an example of the SQL update statement using MySQL RDBMS. Attached are all the syntaxes we used.

Last updated on Jan. 31st, 2022

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.