SQL UPDATE Statement with Practical Examples

SQL UPDATE Statement

SQL Update statement is used to modify the column value or set of column values of particular row or rows. SQL Update statement updates existing records within a table. SQL Update statement is modifying table records in either of the way: update value of all rows or update value of selected row.

 

SQL UPDATE Syntax

This is the syntax to be used while updating specified column value(s) of specific record depending on WHERE condition:

UPDATE table 
SET 
   column1 = constant_value1 | expression | DEFAULT | NULL, 
   column2 = constant_value2 | expression | DEFAULT | NULL, 
   column3 = constant_value3 | expression | DEFAULT | NULL,
   ... 
WHERE condition 

Here,

• Table: Represents the table or view from which the rows are to be updated.
• column1: name of first, second, third column....
• constant_value1 | expression | DEFAULT | NULL: constant_value  describes modified value of column, expression defines any set of arithmetic expression, DEFAULT specifies default value for this column specified in table structure or NULL.
• WHERE condition: WHERE clause is used to select the rows for which the columns are needed to be updated. the WHERE clause is used to choose the particular record of table.

 

SQL UPDATE | Examples

In the first section we are restricting to take some of the examples of SQL UPDATE statement to learn how to write update query, for that we are taking one sample database with two tables one is tblemp and another is tbldept also inserting some records in both the tables.

Note: Consider original data records of both the tables for each example of following section.

Employee Table (tblemp)

Department Table (tbldept)

 

Example-1: Updating Multiple Rows

In this section we have covered some examples of SQL UPDATE statement by updating all Records of column:

How will the SQL Update work if the salary of all employees are to be increased by 2%:

UPDATE tblemp 
SET Salary = Salary * 1.02; 

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘10 rows affected by last query’ or ‘command execute successfully’ depending on editor used.
• To check the result of preceding update statement, we have to use SELECT query to fetch data.

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

Output:

 

Suppose company is decided to shift all departments to Surat location, how can we execute it with SQL Update:

UPDATE tbldept 
SET Dept_location = 'Surat';

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘5 rows affected by last query’ or ‘command execute successfully’ depending on editor used.
• To check the result of preceding update statement, we have to use SELECT query to fetch data.

SELECT Dept_id, Dept_name, Dept_location 
FROM tbldept;

Output:

 

Example-2: Updating rows with WHERE condition

Now we are proceeding with examples of conditional SQL Update statement.

How would we use SQL Update statement to change current location of  Employee with Emp_id '110'  from ‘Kashi’ street to shifting ‘Kailash’:

UPDATE tblemp SET street = 'Kailash' 
WHERE (Emp_id = '110'); 

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘1 row affected by last query’ based on condition one record is having emp_id as 110.
• To check the result of preceding update statement, we have to use select query to fetch data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

For example with SQL conditional update statement rise salary of employee who draw the salary less than 20,000 with 2% increment:

UPDATE tblemp 
SET Salary = Salary * 1.02 
WHERE (Salary < 20000);

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘2 row affected by last query’ based on condition two employee (emp_id: 102,109) is having salary less than 20000.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

With SQL multiple columns value update, change location and city value of employee named yogi to adajan , Surat:

UPDATE       tblemp 
SET street = 'adajan', city = 'Hazira'
WHERE (Emp_name = 'yogi');

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘1 row affected by last query’ based on condition one employee (Emp_name=’yogi’).
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

SQL UPDATE Statement | Scalar Subqueries

Scalar Subqueries (a subquery that selects only one column or expression and returns one row) are also useful in SQL Update statement, where they can be used in the SET clause to set expression value for column.

Scalar Subqueries can be use with the VALUES clause of an INSERT statement, the WHERE clause of a SELECT, the SET clause of an UPDATE statement, and the WHERE clause of a DELETE statement.

Scalar subqueries can be used to compute several different types of aggregations (max and avg) all in the same SQL statement.

 

To update contact detail of employee named annat with the contact detail of employee yogi how can we use Scalar subquery in SQL Update:

UPDATE tblemp SET Emp_contact =  (SELECT Emp_contact FROM   tblemp AS tblemp_1 
WHERE (Emp_name = 'annant'))
WHERE (Emp_name = 'yogi');

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘1 row affected by last query’, SQL first execute scalar subquery which returning contact detail of employee annat which will place as a value of employee yogi.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

Using SQL Scalar subquery within update statement increase salary of employee who are working in sales department:

UPDATE tblemp SET Salary = Salary + 1000 
WHERE (Dept_id = (SELECT Dept_id FROM tbldept
WHERE (Dept_name = 'Sales')));

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘3 row affected by last query’, SQL first execute scalar subquery which return department id of sales department which will used as condition in outer SQL Update statement.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

SQL UPDATE statement | CASE Construct

SQL provides a CASE construct that we can use to perform to set the value of one column depending upon the values in other columns.

The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements. The WHEN statement specifies the condition to be tested. The THEN statement specifies the action if the WHEN condition returns TRUE.

The ELSE statement is optional and executes when none of the WHEN conditions return true. The CASE statement ends with an END keyword.

Case Statement has one limitation it is executing like sequential model. If one condition is become true, it stops execution not verifying further conditions.

Syntax:

UPDATE table_name  
set column_name= CASE column_name 
WHEN condition1 THEN result1 
WHEN condition2 THEN result2 
 ... 
ELSE result 
END

 

Case construct in SQL Update statement Examples

Substitute each department name value with its capitalization font format by placing case construct in SQL update statement:

UPDATE tbldept 
SET  Dept_name = CASE Dept_name  
WHEN 'sales' THEN 'Sales'  
WHEN 'account' THEN 'Account'  
WHEN 'marketing' THEN 'Marketing'  
WHEN 'finance' THEN 'Finance'  
ELSE NULL  
END

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘4 row affected by last query’, comparing each department name value with WHEN clause condition value than modify it with new case value.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Dept_id, Dept_name, Dept_location 
FROM tbldept;

 

SQL UPDATE Statement with RegEx

REGEXP is the pattern or a regular expression operator represents the pattern to be matched by REGEXP.

Suppose there is situation in which we need to modify table record base on Patten matching or need to update column value with any specific format in such condition we can used RegEx with SQL UPDATE statement with REPLACE OR CONCAT function.

Let's take Example of Regular expression with SQL Update statement:

Suppose we wish to start all contact number of employees with’+91’ for that we execute SQL Update with RegEx and concat function:

UPDATE tblemp 
SET Emp_contact = { fn CONCAT ('+91', Emp_contact) };

• After writing the query, click on the execute SQL button to execute query.
• Once the query is executed, a message appears like ‘10 row affected by last query’, in this query we use CONCAT function to concatenate RefEx ‘+91’ with Contact number of all employees.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT:

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp;

 

Summary

This Write-up covers SQL UPDATE Statement, which is DML (data manipulation language) statement of SQL and used to change or modify existing record of a SQL table. We have converged various permutations of SQL UPDATE statement like with conditions, Clauses, Case construct, Scalar Subqueries and in other contexts.

 

Further Reading

https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj26498.html