dark

How to SQL Update From Select

How to SQL Update From Select

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.

Prerequisites

Before we start, ensure that you have:

  • Access to a SQL database
  • Basic understanding of SQL syntax
  • Basic knowledge of the SELECT and UPDATE statements

Updating Rows with Data from Another Table

The 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;
  • table1 is the table you want to update.
  • table2 is the table that contains the data you want to use to update table1.
  • key is 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: customers and 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 orders table.

The WHERE clause joins the two tables together using the customer_id column.

Output

customer_idnametotal_orders
1John3
2Sarah2
3Jack1

Troubleshooting Tips

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.

Conclusion

The 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.

Related tutorials