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

Updating from another table is common for applying lookups like discount rates. Each dialect expresses the join differently.

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?

Updating from another table is common for applying lookups like discount rates. Each dialect expresses the join differently. 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.