UPDATE with subquery preview in SQL

Introduction

We use UPDATE with a subquery to change data in one table based on information from another table. It helps keep data accurate and connected.

When you want to update a customer's address based on their latest order details.

When you need to set employee salaries based on their department's average salary.

When you want to mark products as discontinued if they haven't sold in the last year.

When you want to update student grades based on scores stored in another table.

Syntax

SQL

UPDATE table_to_update
SET column_to_change = (SELECT value FROM another_table WHERE condition)
WHERE some_condition;

The subquery inside parentheses returns the new value for the column.

Make sure the subquery returns only one value for each row to update.

Examples

Update each employee's department name by looking it up from the departments table.

SQL

UPDATE employees
SET department = (SELECT dept_name FROM departments WHERE departments.id = employees.dept_id)
WHERE dept_id IS NOT NULL;

Set the price of all books to the average price of books.

SQL

UPDATE products
SET price = (SELECT AVG(price) FROM products WHERE category = 'Books')
WHERE category = 'Books';

Sample Program

We create two tables: employees and departments. Then we update the employees table to fill the department name by looking it up from departments using dept_id.

SQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT, department VARCHAR(20));
CREATE TABLE departments (id INT, dept_name VARCHAR(20));

INSERT INTO employees VALUES (1, 'Alice', 10, NULL), (2, 'Bob', 20, NULL), (3, 'Carol', 10, NULL);
INSERT INTO departments VALUES (10, 'Sales'), (20, 'HR');

UPDATE employees
SET department = (SELECT dept_name FROM departments WHERE departments.id = employees.dept_id)
WHERE dept_id IS NOT NULL;

SELECT id, name, dept_id, department FROM employees ORDER BY id;

Important Notes

If the subquery returns more than one value, the UPDATE will fail with an error.

You can use JOINs instead of subqueries for better performance in some databases.

Always backup your data before running UPDATE queries to avoid accidental data loss.

Summary

UPDATE with subquery lets you change data based on other tables.

The subquery must return a single value for each row updated.

It helps keep related data consistent and up to date.