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
|
1 2 3 |
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_namein 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.
|
1 2 |
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:
|
1 |
SELECT * FROM Employee; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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, let’s update the last name using the UPDATE query shown below.
|
1 2 3 |
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:
|
1 2 3 4 5 |
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.
Let’s now update 2 Columns i.e Salary and Department using the UPDATE query shown below.
|
1 2 3 |
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:
|
1 |
SELECT * FROM Employee; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 |
UPDATE Employee SET Salary=90000 WHERE Salary>=60000; |
The above SQL statement updates salary of 2 employees (101,104) to 90000 whose salary was >=60000.
CHECK OUTPUT:
|
1 2 3 4 5 6 7 |
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 + , – , * , /
|
1 2 3 |
UPDATE Employee SET Salary=Salary+7000 WHERE Salary<40000; |
The above SQL statement updates salary of one employees (102) by 7000 whose salary was less than 40000
CHECK OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 |
UPDATE Employee SET First_name= ‘Mr.’ || First_name WHERE ID=102; |
The above SQL statement updates First name of employee whose ID is 102
|
1 2 3 |
UPDATE Employee SET City= City || ‘New’ WHERE ID=105; |
CHECK OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
