Update Query in SQL: Comprehensive Guide

In this extensive article, we will explore “Update Query in SQL” with various examples. Let’s get started.

Table of Contents

What is Update Query in SQL ?

An Update Query in SQL is a structured database operation used to modify existing records or rows in a database table. It allows you to change the values of one or more columns in one or more rows of a table, based on specified criteria. Update Queries are fundamental to maintaining and managing the data within a relational database system.

The basic syntax of an Update Query in SQL is as follows:

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

Here’s a breakdown of the components:

UPDATE: This keyword signals the beginning of the update operation.
table_name: Specifies the name of the table you want to update.
SET: Indicates the columns that you want to update and the new values you want to assign to them.
column1, column2, …: These are the columns you want to update. You can list multiple columns, each separated by a comma.
value1, value2, …: These are the new values you want to set for the corresponding columns. Each value corresponds to the column listed in the SET clause.
WHERE: This optional clause allows you to specify the condition that determines which rows will be updated. If you omit the WHERE clause, all rows in the table will be updated.
condition: The condition is used to filter the rows that will be updated. Only rows that meet the condition will have their values changed.

Example: Suppose you have a table called “employees” and you want to increase the salary of all employees with a job title of “Manager” by 10%. You can use an SQL Update Query like this:

UPDATE employees
SET salary = salary * 1.10
WHERE job_title = 'Manager';

Update Query in SQL Examples

Let’s look into various examples for using Update Query in SQL.

Update a single value in a row

UPDATE Customers SET first_name = 'Johnny' WHERE customer_id = 1;

This query updates the first_name column of the Customers table to ‘Johnny’ for the row where the customer_id is

Update multiple values in a row

UPDATE Customers SET first_name = 'Johnny', last_name = 'Depp' WHERE customer_id = 1;

This query updates the first_name and last_name columns of the Customers table to ‘Johnny’ and ‘Depp’, respectively, for the row where the customer_id is 1.

Update multiple rows that satisfy a condition

UPDATE Customers SET country = 'US' WHERE age > 21;

This query updates the country column of the Customers table to ‘US’ for all rows where the age is greater than 21.

Update all rows in a table

UPDATE Customers SET country = 'US';

This query updates the country column of the Customers table to ‘US’ for all rows in the table.

Update a value based on the value of another column

UPDATE Customers SET salary = salary * 1.1 WHERE department = 'Sales';

This query updates the salary column of the Customers table by multiplying the current salary by 1.1 for all rows where the department is ‘Sales’.

Using Subqueries in Update Queries in SQL

Here are some examples of using subqueries in UPDATE queries in SQL:

Update Using a Subquery to Set a Single Column Value

Suppose you have a table called products and another table called discontinued_products. You want to mark products as discontinued if their product_id is listed in the discontinued_products table.

UPDATE products
SET status = 'Discontinued'
WHERE product_id IN (SELECT product_id FROM discontinued_products);

Updating a Single Column Based on a Subquery Result

Suppose you have a table employees and you want to update the salary of all employees with a job title of ‘Manager’ to be 10% higher than the average salary of all employees.

UPDATE employees
SET salary = (SELECT AVG(salary) * 1.10 FROM employees WHERE job_title = 'Manager')
WHERE job_title = 'Manager';

Updating Based on Aggregated Subquery Results

You can also update data based on aggregated subquery results. For instance, updating the total_sales column in a salespeople table with the sum of sales amounts for each salesperson from a sales table.

UPDATE salespeople s
SET total_sales = (
    SELECT SUM(sale_amount)
    FROM sales
    WHERE salesperson_id = s.salesperson_id
);

Correlated subqueries for updates

You can use a correlated subquery to update a column based on a related value from another table. For example, updating the order_status in an orders table based on the latest shipment_status from a related shipments table.

UPDATE orders o
SET order_status = (
    SELECT shipment_status
    FROM shipments s
    WHERE s.order_id = o.order_id
    ORDER BY s.shipment_date DESC
    LIMIT 1
);

Updating Multiple Columns Using Subqueries

You can update multiple columns simultaneously using subqueries. For instance, updating the total_cost and quantity columns in an order_details table based on the corresponding product prices and ordered quantities from a products table.

UPDATE order_details od
SET total_cost = (
    SELECT p.price * od.quantity
    FROM products p
    WHERE p.product_id = od.product_id
),
quantity = (
    SELECT p.stock_quantity
    FROM products p
    WHERE p.product_id = od.product_id
);

Updating with Conditional Subqueries

You can use subqueries with conditional logic to update rows selectively. For example, updating the discount column in a products table based on a category-specific discount percentage.

UPDATE products
SET discount = CASE
    WHEN category = 'Electronics' THEN (SELECT discount_percentage FROM discounts WHERE category = 'Electronics')
    WHEN category = 'Clothing' THEN (SELECT discount_percentage FROM discounts WHERE category = 'Clothing')
    ELSE 0  -- Default discount if no specific category match
END;

Update using Subqueries with EXISTS and NOT EXISTS

Using EXISTS and NOT EXISTS in subqueries allows you to update records based on whether certain conditions are met or not met in another table. Here are examples of using both EXISTS and NOT EXISTS in update subqueries:

1. Using EXISTS in an Update Subquery:

Suppose you have two tables: orders and order_payments. You want to update the payment_status column in the orders table to ‘Paid’ if there is at least one corresponding payment record in the order_payments table.

UPDATE orders o
SET payment_status = 'Paid'
WHERE EXISTS (
    SELECT 1
    FROM order_payments op
    WHERE op.order_id = o.order_id
);

In this example:

We’re updating the payment_status column in the orders table.
The subquery (SELECT 1 FROM order_payments op WHERE op.order_id = o.order_id) checks if there is at least one payment record in the order_payments table for each order.
If the subquery returns any results (i.e., there’s at least one payment record), the payment_status is updated to ‘Paid’ for that order.

2. Using NOT EXISTS in an Update Subquery:

Suppose you have a table called employees and another table called terminated_employees. You want to mark employees as ‘Terminated’ in the employees table if they are not found in the terminated_employees table.

UPDATE employees e
SET employment_status = 'Terminated'
WHERE NOT EXISTS (
    SELECT 1
    FROM terminated_employees te
    WHERE te.employee_id = e.employee_id
);

In this example:

We’re updating the employment_status column in the employees table.
The subquery (SELECT 1 FROM terminated_employees te WHERE te.employee_id = e.employee_id) checks if there is no corresponding record in the terminated_employees table for each employee.
If the subquery returns no results (i.e., the employee is not found in the terminated_employees table), the employment_status is updated to ‘Terminated’ for that employee.

Best Practices for Using Update Query in SQL

Here are some best practices to follow when writing update queries in SQL:

Always Backup Data: Before performing updates on critical data, it’s a good practice to make a backup of the data or have a rollback plan in case something goes wrong.
Use Transactions: When updating multiple records, use transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure data consistency. This way, all changes are made atomically, and you can roll back if there are any errors.
Use WHERE Clause Selectively:
- Include a WHERE clause to specify which records to update. Updating all records in a table can have unintended consequences.
- Be specific in your WHERE clause to target only the necessary rows.
- Use caution with UPDATE queries that don’t have a WHERE clause, as they can update all rows in the table.
Test Your Queries: Always test your update queries in a safe environment (e.g., a development or staging database) before applying them to production data.
Avoid Subqueries When Not Necessary: While subqueries can be useful, they can also slow down updates. If you can achieve the same result using a join or other methods, consider those alternatives.
Prevent SQL Injection:
- Use parameterized queries or prepared statements to sanitize user input and prevent SQL injection attacks (as discussed in a previous response).
- Avoid directly embedding user input in your SQL queries.
Use a batch update to update multiple rows at once: This can improve performance for large tables.
Use a stored procedure to encapsulate your update logic: This can make your code more reusable and maintainable.

Conclusion: Update Query in SQL

In this extensive article, we delved into the “Update Query in SQL”, exploring the syntax, usage, and various examples. We began by understanding the fundamental concept of an Update Query in SQL, which serves as a powerful tool for modifying existing records in a database table. The basic syntax, comprising keywords like UPDATE, SET, WHERE, and their respective roles, was dissected to provide a comprehensive understanding.

We then ventured into practical examples, demonstrating how to use Update Queries effectively in different scenarios. We explored techniques such as updating a single value in a row, updating multiple values in a row, and updating multiple rows based on specific conditions. We even touched upon updating data using subqueries, showcasing various subquery types like correlated subqueries, aggregated subqueries, and conditional subqueries.

Additionally, we discussed the crucial aspect of preventing SQL injection in Update Queries and emphasized the use of parameterized queries or prepared statements to secure our database operations.

Related Articles :