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

  • 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. 

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:

Example: 1

Now, let’s update the last name using the UPDATE query shown below.

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

CHECK OUTPUT:

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.

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

CHECK OUTPUT:

Example: 3

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

Lets now UPDATE multiple records using below query.

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

CHECK OUTPUT:

Example 4:

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

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

CHECK OUTPUT:

Example 5:

You can also prepend or append text fields

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

CHECK OUTPUT:

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

Add a Comment

Your email address will not be published. Required fields are marked *