SQL for QA: Tutorial#4 – UPDATE Statement

UPDATE Statement is also frequently used by Testers and QAs. UPDATE is used to modify already existing records from the database table.

In this article we will see how we can modify single as well as multiple Columns of a record at a time. Also update a table with values from another table using UPDATE command

Syntax

UPDATE Table_name
SET [Column1 = Value1, Column2 = Value2...., ColumnN = ValueN]
WHERE [Condition];

• SET keyword in UPDATE command is used to UPDATE the Column values.
• `Table_name` in UPDATE command tells to UPDATE the data from a given table .
• Where clause is optional in UPDATE command. It specifies which records that should get UPDATEed, but if we omit where clause, all records in the table get Updated.

For eg.            

UPDATE Customer_Table
SET CustName='Sammy';

In above example where clause is absent, so all the records in the Customer table for CustName Column Updated with value Sammy.

• In where clause we can combine many conditions using AND, OR operator.

Suppose, we have a following table with sample data inserted into it

Sample Table:

SELECT * FROM Employee;

ID | First_name | Last_name  | City    | Salary | Department

-----------+------------+------------+--------+--------+-----------

101 | Jenny     | Wadra      | Pune    | 85800  | HR

102 | Mak       | Patty      | Mumbai  | 18000  | IT

103 | John      | Roy        | Pune    | 30800  | Admin

104 | Sammy     | Desuza     | Pune    | 66000  | IT

105 | Rosy      | Shenoy     | Mumbai  | 40000  | HR

106 | Rosy      | Gomes      | Pune    | 42000  | Testing

Example: 1

Now, letxs update the last name using the UPDATE query shown below.

UPDATE Employee
SET  Last_name='K.'
WHERE ID=104;

After successful execution of the above query, it UPDATEs the last name from Desuza to K. for ID 104.

CHECK OUTPUT:

ID | First_name | Last_name | City  | Salary | Department

---|------------+-----------+-------+--------+-----------

104| Sammy     | K.          | Pune  | 66000  | IT

Example: 2

Suppose we want to update multiple columns for the same record then use the Comma operator with the columns written in “SET” keyword.

Letxs now update 2 Columns i.e Salary and Department using the UPDATE query shown below.

UPDATE Employee
SET Salary=40000,Department='IT'
WHERE ID=103;

After execution of above query, 2 Columns Salary and Department got Updated for ID    103.

CHECK OUTPUT:

SELECT * FROM Employee;

ID  | First_name | Last_name | City   | Salary | Department

----|------------+-----------+---------+--------+-------------

102 | Mak       | Patty     | Mumbai  | 18000  | IT

105 | Rosy      | Shenoy    | Mumbai  | 40000  | HR

106 | Rosy      | Gomes     | Pune    | 42000  | Testing

101 | Jenny     | Wadra     | Pune    | 85800  | HR

104 | Sammy     | K.        | Pune    | 66000  | IT

103 | John      | Roy       | Pune    | 40000  | IT

Example: 3

We can also update multiple records by writing condition in WHERE clause

Lets now UPDATE multiple records using below query.

UPDATE Employee SET Salary=90000  WHERE Salaryx=60000;

The above SQL statement updates salary of 2 employees (101,104) to 90000 whose salary was x=60000.

CHECK OUTPUT:

ID  | First_name | Last_name | City   | Salary   | Department

-----------+------------+-----------+--------+--------+-----------

101 | Jenny      | Wadra     | Pune   | 90000    | HR

104 | Sammy      | K.        | Pune   | 90000    | IT

Example 4:

Here you can also use simple arithmetic operators like + , x , * , /

UPDATE Employee
SET Salary=Salary+7000
WHERE Salaryx40000;

The above SQL statement updates salary of one employees (102) by 7000 whose salary was less than 40000

CHECK OUTPUT:

ID  | First_name | Last_name |  City   | Salary | Department

----|------------+-----------+---------+--------+-----------

102 | Mak       | Patty      | Mumbai  | 25000  | IT

105 | Rosy      | Shenoy     | Mumbai  | 40000  | HR

106 | Rosy      | Gomes      | Pune    | 42000  | Testing

103 | John      | Roy        | Pune    | 40000  | IT

101 | Jenny     | Wadra      | Pune    | 90000  | HR

104 | Sammy     | K.         | Pune    | 90000  | IT

Example 5:

You can also prepend or append text fields

UPDATE Employee
SET First_name= ‘Mr.’ || First_name
WHERE ID=102;

The above SQL statement updates First name of employee whose ID is 102

UPDATE Employee
SET City= City || ‘New’
WHERE ID=105;

CHECK OUTPUT:

ID   | First_name | Last_name |  City      | Salary  | Department

--- -+------------+-----------+------------+---------+------------

102  | Mr. Mak    | Patty     | Mumbai     | 25000   | IT

105  | Rosy       | Shenoy    | Mumbai New | 40000   | HR

106  | Rosy       | Gomes     | Pune       | 42000   | Testing

103  | John       | Roy       | Pune       | 40000   | IT

101  | Jenny      | Wadra     | Pune       | 90000   | HR

104  | Sammy      | K.        | Pune       | 90000   | IT

Conclusion

In this article, we have briefly seen the Update Statment. Please feel free to comment or suggest the enhancements to the contents. Next, we will be sharing information on the DELETE statement. Stay Tuned!!

Tutorial Index:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause