Sometimes, you need to update rows in one table based on data from another table. The SQL statement to achieve this is called an
UPDATE FROM SELECT statement. In this tutorial, you will learn how to use this statement with ease.
Before we start, ensure that you have:
- Access to a SQL database
- Basic understanding of SQL syntax
- Basic knowledge of the
Updating Rows with Data from Another Table
UPDATE FROM SELECT statement is used when you want to update rows in one table with data from another table. Here is the basic syntax:
UPDATE table1 SET column1 = table2.column1, column2 = table2.column2, ... FROM table2 WHERE table1.key = table2.key;
table1is the table you want to update.
table2is the table that contains the data you want to use to update
keyis the column that joins the two tables together.
Let’s take a look at an example.
Example: Updating Rows with Data from Another Table
Suppose you have two tables:
orders. You want to update the
customers table with the total number of orders each customer has made. Here’s how you would use the
UPDATE FROM SELECT statement:
UPDATE customers SET total_orders = order_count FROM ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) AS temp WHERE customers.customer_id = temp.customer_id;
In this example, we are updating the
customers table and setting the
total_orders column to the
order_count column from the subquery. The subquery selects the
customer_id and the number of orders for each customer from the
WHERE clause joins the two tables together using the
When using the
UPDATE FROM SELECT statement, ensure that:
- The tables you are joining have a common column.
- The common column is indexed in both tables.
- You have the necessary permissions to update the table.
UPDATE FROM SELECT statement is a powerful tool in SQL that allows you to update rows in one table based on data from another table. In this tutorial, you learned how to use this statement and how to troubleshoot any issues that may arise.