SQL Update From Select

Galaxy Glossary

How can I update multiple rows in a table based on values from another table?

The `UPDATE ... FROM` clause in SQL allows you to update rows in one table using data from another table. This is a powerful technique for data manipulation, especially when you need to synchronize or modify data across multiple tables.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The `UPDATE ... FROM` clause is a powerful SQL feature that allows you to update rows in one table based on data from another table. Instead of manually specifying each row to update, you can use a `SELECT` statement to identify the rows that need modification. This is particularly useful when you need to synchronize data between tables or perform bulk updates based on criteria from another table. It's important to understand that the `FROM` clause in this context refers to the table whose data you're using to update the target table, not a join operation. The `WHERE` clause is still crucial to specify the exact rows to update. This method is more efficient than using multiple `UPDATE` statements or a loop in your application, as it's handled entirely within the database engine.

Why SQL Update From Select is important

This technique is crucial for data synchronization and maintenance. It allows for efficient bulk updates, reducing the need for complex application logic and improving performance. It's a fundamental skill for any SQL developer.

Example Usage


-- SQL Server example
SELECT TOP 5 CustomerName, OrderDate
FROM Customers
ORDER BY OrderDate DESC;

-- MySQL example
SELECT CustomerName, OrderDate
FROM Customers
ORDER BY OrderDate DESC
LIMIT 5;

Common Mistakes

Forgetting the `WHERE` clause, leading to unintended updates across all rows.
Using incorrect table aliases or column names, resulting in errors.
Misunderstanding the `FROM` clause's role in the `UPDATE` statement.
Not considering potential data conflicts or inconsistencies when updating data from another table.

SQL Update From Select

How can I update multiple rows in a table based on values from another table?

Description

Why SQL Update From Select is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL