Doltgres Now Supports UPDATE ... FROM

We're excited to announce support for UPDATE ... FROM in Doltgres, our Postgres-compatible, version-controlled database.

This feature brings Doltgres closer to being compatible with PostgreSQL and unlocks a common, and powerful, SQL feature for writing efficient, expressive UPDATE statements, especially when working across multiple related tables.

In this post, we'll walk through what the UPDATE ... FROM statement does, why it's useful, and show some examples with Doltgres.

What Is UPDATE ... FROM?

PostgreSQL allows you to write an UPDATE query that pulls in data from other tables using a FROM clause. This syntax makes it easy to update rows based on a join – no need for subqueries or procedural code.

Basic syntax:

UPDATE target_table
SET column = expression
FROM other_table
WHERE condition;

You can reference columns from other_table in your SET and WHERE clauses, as long as the join is well-formed. Note that unlike MySQL's version of this feature (UPDATE ... JOIN), Postgres limits you to only updating target_table. Your UPDATE ... FROM statement may not update any of the additional tables in the FROM clause.

Why is UPDATE ... FROM Useful?

Without UPDATE ... FROM, you'd often need to resort to subqueries or writing procedural looping code to copy or sync data between tables. By supporting UPDATE ... FROM in Doltgres, you can write cleaner, more readable SQL UPDATE statements without having to resort to subqueries, and the execution can often be more efficient for updates over large datasets. In the next section, we'll see a concrete example of an UPDATE query that uses a FROM clause and what an equivalent query would look like without using the FROM clause.

A Simple Example

Let's say we have an employees table and a departments table. Each employee belongs to one department. The SQL statements below create those tables and insert a couple rows of sample data:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department_id INT,
  salary INT
);

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name TEXT,
  bonus INT
);

INSERT INTO employees (name, department_id, salary) VALUES
  ('Alice', 1, 50000),
  ('Bob', 2, 55000);

INSERT INTO departments (name, bonus) VALUES
  ('Engineering', 5000),
  ('Marketing', 3000);

We're feeling generous, so we want to apply a department-wide bonus to every employee's salary.

Without UPDATE ... FROM support, a common approach is to use a correlated subquery in the SET clause:

UPDATE employees
SET salary = salary + (
    SELECT bonus
    FROM departments
    WHERE departments.id = employees.department_id
);

This approach works, however, it is a bit hard to read, looks complex, and may be less performant for large datasets due to the repeated subquery evaluation.

With UPDATE ... FROM support, we can write the query like this:

UPDATE employees
SET salary = salary + departments.bonus
FROM departments
WHERE employees.department_id = departments.id;

This version is clearer, faster to write, and often executes more efficiently. The FROM clause makes the join relationship explicit, and the SET clause directly references columns from the joined table, making the query very easy to understand.

After running these statements in Doltgres, we can query the employees table and see that the salaries have been correctly updated:

select * from employees;
 id | name  | department_id | salary 
----+-------+---------------+--------
  1 | Alice |             1 |  55000
  2 | Bob   |             2 |  58000
(2 rows)

A Multi-table Example

The previous example updated the employees table by joining against a single table, but you aren't limited to using just a single table in your FROM clause. Here's a slightly more advanced UPDATE ... FROM statement that joins against two tables. The locations table is joined on the department's location, and we filter employees getting a bonus to the "West Coast" region:

UPDATE employees
SET salary = salary + bonus
FROM departments, locations
WHERE employees.department_id = departments.id
  AND departments.location_id = locations.id
  AND locations.region = 'West Coast';

This statement works similarly to the previous example, it just has a slightly larger join.

What's Next?

We're continuing to steadily expand Doltgres' compatibility with PostgreSQL, including support for SQL syntax, Postgres native functions, tooling like workbenches and ORMs, and even Postgres extensions. Check out our Getting Started instructions if you want to kick the tires yourself. If you hit any bugs or missing features, send us an issue on GitHub and we'll be happy to prioritize it for you. If you just want to chat with our dev team or other Dolt and Doltgres customers, come on over to our Discord server and say hello.