UPDATE

Estimated reading: 3 minutes 160 views

SQL’s UPDATE command is one of the most commonly used Data Manipulation Language (DML) statements in database management. It allows you to modify existing records in a table by updating one or more fields based on specified conditions. For software testers, mastering the UPDATE command is crucial for tasks such as modifying test data, simulating various test scenarios, or validating data updates.

Optional Commands

Command Option	Description	Example
`SET`	Specifies the column(s) to update and their new values.	`SET salary = 5000`
`WHERE`	Defines the condition to identify the rows to update.	`WHERE department = 'HR'`
`FROM` (optional)	Allows updates based on a join with another table.	`UPDATE emp SET emp.salary = dep.avg_salary FROM ...`
`RETURNING` (optional)	Returns the updated records (supported in some databases like PostgreSQL).	`RETURNING id, name`

Syntax and Example

Basic Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example 1: Updating a Single Column

Suppose you have an employees table with the following data:

id	name	salary	department
1	John Doe	4000	HR
2	Jane Smith	5000	IT
3	Alice Lee	4500	HR

If you want to increase the salary of all employees in the HR department by 10%, you can use the following SQL:

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'HR';

After executing this query, the table will look like this:

id	name	salary	department
1	John Doe	4400	HR
2	Jane Smith	5000	IT
3	Alice Lee	4950	HR

Example 2: Updating Multiple Columns

To move John Doe to the IT department and update his salary to 5200, use:

UPDATE employees
SET department = 'IT', salary = 5200
WHERE name = 'John Doe';

Example 3: Conditional Updates Using Subqueries

You can update values based on a subquery. For instance, if you have another table department_avg that contains the average salary for each department:

department	avg_salary
HR	4800
IT	5500

To set the salary of all employees to match the average salary of their department, use:

UPDATE employees
SET salary = (SELECT avg_salary FROM department_avg WHERE department_avg.department = employees.department);

Why Use the UPDATE Command?

Test Data Modification: As a software tester, you may need to adjust test data for specific scenarios. For example, updating user roles, changing status values, or modifying dates.
Simulating Real-World Scenarios: The UPDATE command can help simulate scenarios where data changes dynamically, such as updating order statuses or user account details.
Validating Application Behavior: Testing how an application handles updated data is essential, especially for CRUD operations.
Efficient Bulk Updates: Instead of modifying individual rows manually, the UPDATE command allows for bulk updates, saving time and effort.

Conclusion

The UPDATE command is a powerful tool in SQL for modifying existing data in a database. As a software tester, it enables you to prepare and manipulate test data efficiently, simulate complex scenarios, and validate application functionality. By understanding its syntax and use cases, you can leverage the UPDATE command to enhance your database testing skills and improve the quality of your testing efforts.