sql

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;
  • Table_name = the name of the relation/table whose records need to be changed.
  • column_name = the name of the column/field that is to be updated.
  • Value = the new record value that needs to be set for that field
  • Condition = To restrict the records updated.
  • 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:

    SQL Update - all tables in testdb
    all tables in testdb

    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.

    sql Update statement  for single row
    Update statement for single row

    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;
    
    SQL Update - Records with Last name as null.
    Records with Last name as 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.

    SQL Update - removing the Safe option in MySQL Workbench
    removing the Safe option in MySQL Workbench

    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.

    sql Update statement for multiple rows
    Update statement for multiple rows

    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

    Update statement with replace
    Update statement with replace

    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.

    Update with select in a subquery
    Update with select in a subquery

    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

    Update statement with select
    Update statement with select

    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);
    

    update with random
    Update with Random

    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.

    Update statement with a inner join.
    Update statement with an inner join.

    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.

    update statement with left join
    update statement with left join

    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.

    Download
    You can download the full source code of this example here: SQL Update Statement Example

    Last updated on Jan. 31st, 2022

    Reshma Sathe

    I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
    Subscribe
    Notify of
    guest

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    0 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments
    Back to top button