Forgly

SQL to update a table from another table

Update rows using values looked up from a second table (UPDATE with JOIN).

PostgreSQL
UPDATE orders o
SET total = o.total * (1 - d.rate)
FROM discounts d
WHERE d.category_id = o.category_id;
MySQL
UPDATE orders o
JOIN discounts d ON d.category_id = o.category_id
SET o.total = o.total * (1 - d.rate);
SQL Server
UPDATE o
SET o.total = o.total * (1 - d.rate)
FROM orders o
JOIN discounts d ON d.category_id = o.category_id;
SQLite
UPDATE orders
SET total = total * (1 - (
  SELECT rate FROM discounts d
  WHERE d.category_id = orders.category_id
))
WHERE category_id IN (SELECT category_id FROM discounts);

SQLite has no UPDATE ... JOIN, so a correlated subquery does the lookup.

How to update a table using a join to another table

To update a table from another table in SQL, join to the second table and set columns from its values — handy for applying lookups like discount rates. Copy-paste examples below for PostgreSQL (UPDATE ... FROM), MySQL (UPDATE ... JOIN), SQL Server (UPDATE alias ... FROM), and SQLite (correlated subquery).

How it works

  • PostgreSQL uses UPDATE ... FROM, MySQL puts the JOIN before SET, and SQL Server updates the table alias with a FROM clause.
  • The WHERE / ON condition matches each order to its discount row.
  • Only rows with a matching discount are updated; the rest stay unchanged.

Tip

Run the equivalent SELECT (same joins and filters) first to confirm exactly which rows will change.

Related SQL queries

Frequently asked questions

How do I update a table using a join to another table in SQL?

To update a table from another table in SQL, join to the second table and set columns from its values — handy for applying lookups like discount rates. Copy-paste examples below for PostgreSQL (UPDATE ... FROM), MySQL (UPDATE ... JOIN), SQL Server (UPDATE alias ... FROM), and SQLite (correlated subquery). PostgreSQL uses UPDATE ... FROM, MySQL puts the JOIN before SET, and SQL Server updates the table alias with a FROM clause. The WHERE / ON condition matches each order to its discount row. Only rows with a matching discount are updated; the rest stay unchanged.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.