How to update one table from another in SQL

1UPDATE … SET … FROM: join the other tableRecommended

Name the source table in a FROM clause after SET, and put the match condition in WHERE. Each target row that finds a partner gets that partner's value; rows without a partner aren't touched. Writing the same join as a SELECT first is a cheap way to see exactly which rows will change before you run the update.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES
    (1, 'Desk', 120.00),
    (2, 'Chair', 45.00),
    (3, 'Lamp', 18.50),
    (4, 'Shelf', 60.00);

CREATE TABLE new_prices (product_id INTEGER, price REAL);
INSERT INTO new_prices VALUES (1, 135.00), (3, 21.00), (9, 5.00);

-- Preview: the same join as a SELECT
SELECT p.id, p.price AS old, np.price AS new
FROM products AS p
JOIN new_prices AS np ON np.product_id = p.id;

-- Copy each new price onto the product with the same id
UPDATE products
SET price = np.price
FROM new_prices AS np
WHERE np.product_id = products.id;

SELECT changes() AS updated;
SELECT id, name, price FROM products;

The preview lists products 1 and 3 with their old and new prices, and changes() confirms the update touched those 2 rows. The Desk goes to 135.0, the Lamp to 21.0, and the Chair and Shelf keep their prices because new_prices has nothing for them. Product 9 in new_prices has no product, so it is simply ignored. Don't list products again inside FROM: the table after UPDATE is already part of the join, and a second copy turns it into a cross join (see the FAQ). UPDATE … FROM needs SQLite 3.33 or later.

2The portable form: SET col = (SELECT …) WHERE EXISTS

A correlated subquery in SET looks up the new value for each row, and it works in every SQL database, including older ones without a join syntax for UPDATE. The catch is the WHERE clause. Without it the update visits every row, and for a row with no match the subquery returns no row, which counts as NULL. The first update below makes that mistake inside a transaction and rolls it back; the second adds WHERE EXISTS with the same match condition.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES
    (1, 'Desk', 120.00),
    (2, 'Chair', 45.00),
    (3, 'Lamp', 18.50),
    (4, 'Shelf', 60.00);

CREATE TABLE new_prices (product_id INTEGER, price REAL);
INSERT INTO new_prices VALUES (1, 135.00), (3, 21.00), (9, 5.00);

-- Wrong: no WHERE, so rows without a match get NULL
BEGIN;
UPDATE products
SET price = (SELECT np.price FROM new_prices AS np
             WHERE np.product_id = products.id);
SELECT id, name, price FROM products;
ROLLBACK;

-- Right: only touch rows that have a match
UPDATE products
SET price = (SELECT np.price FROM new_prices AS np
             WHERE np.product_id = products.id)
WHERE EXISTS (SELECT 1 FROM new_prices AS np
              WHERE np.product_id = products.id);
SELECT id, name, price FROM products;

Without the WHERE, the Chair and Shelf lose their prices: both come back as NULL, because nothing in new_prices matched them. With WHERE EXISTS only the Desk and Lamp change, the same result as section 1. WHERE id IN (SELECT product_id FROM new_prices) filters the same rows when the match is on one column. The subquery must return at most one row per target row: SQLite quietly uses the first one, while PostgreSQL stops with more than one row returned by a subquery used as an expression. On Oracle, this is the form that works on every version.

3Update several columns at once

With UPDATE … FROM, list each column in SET, separated by commas; every assignment reads from the same matched row. Each assignment can be an expression, so a source table that holds only some changes can leave the other columns alone with COALESCE(new, current), where employees.salary is the row's current value.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
    (1, 'Ana', 'Sales', 52000),
    (2, 'Ben', 'Support', 41000),
    (3, 'Caro', 'Sales', 58000);

-- NULL in a change row means "keep the current value"
CREATE TABLE changes (emp_id INTEGER, dept TEXT, salary INTEGER);
INSERT INTO changes VALUES (1, 'Finance', 61000), (2, 'Sales', NULL);

UPDATE employees
SET dept = c.dept,
    salary = COALESCE(c.salary, employees.salary)
FROM changes AS c
WHERE c.emp_id = employees.id;

SELECT * FROM employees;

Ana moves to Finance on 61000. Ben moves to Sales and keeps his 41000, because his change row has NULL for the salary and COALESCE falls back to the current value. Caro has no change row and is untouched. In the subquery form you would need one subquery per column, or a row-value assignment: SET (dept, salary) = (SELECT c.dept, c.salary FROM changes AS c WHERE …). SQLite and PostgreSQL accept that; MySQL and SQL Server reject it as a syntax error, so use their join forms from section 5 there.

4Update from an aggregate: totals per customer

To store a count or a sum, aggregate first and join the result. Joining the detail table directly is a common mistake: a customer with three orders matches three rows, and an UPDATE writes each target row only once, from one of those matches, not their sum. Put the GROUP BY in a subquery in FROM so each customer meets exactly one row.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, orders INTEGER DEFAULT 0, total REAL DEFAULT 0);
INSERT INTO customers (id, name) VALUES (1, 'Acme'), (2, 'Globex'), (3, 'Initech');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);
INSERT INTO orders (customer_id, amount) VALUES
    (1, 250.00), (1, 100.00), (2, 75.50), (1, 40.00), (2, 30.00);

-- Wrong: joining orders directly matches several rows per customer,
-- and UPDATE uses just one of them
BEGIN;
UPDATE customers SET total = o.amount
FROM orders AS o
WHERE o.customer_id = customers.id;
SELECT id, name, total FROM customers;
ROLLBACK;

-- Right: aggregate first, then join one row per customer
UPDATE customers
SET orders = t.n,
    total = t.total
FROM (SELECT customer_id, COUNT(*) AS n, SUM(amount) AS total
      FROM orders
      GROUP BY customer_id) AS t
WHERE t.customer_id = customers.id;

SELECT * FROM customers;

The direct join stores a single order amount, 40.0 for Acme and 30.0 for Globex; here that happened to be each customer's last order, but which match wins isn't defined. Aggregating first gives Acme 3 orders worth 390.0 and Globex 2 worth 105.5. Initech has no orders, so it has no row in the subquery and keeps its defaults of 0. The portable version is a correlated aggregate, SET total = COALESCE((SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.id), 0), which needs no WHERE EXISTS: the COALESCE turns the empty sum into 0 on purpose.

5Other databases: PostgreSQL, MySQL and SQL Server

The join form is spelled differently in each database, while the correlated subquery from section 2 is the same everywhere. Each join form below and the subquery form were run on PostgreSQL 18, MySQL 8.4 and SQL Server (Babelfish) with the products example from section 1, and gave the same prices. The row-value form ran on PostgreSQL and failed on the other two.