sqlcmd.net validated sql reference

Command guide

advanced updating MySQL MariaDB SQL Server PostgreSQL SQLite

Update Rows Using Values From Another Table

Use a join inside an `UPDATE` statement to copy or derive values from a related table.

5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Copy department names from a lookup table into the employees table

All three employees have their dept_name filled from the matching row in departments. Ada and Carol both map to Engineering (dept_id 1); Bob maps to Finance (dept_id 2). The NULL values are replaced in a single statement.

MySQL MariaDB
Engine-specific syntax
Source table data
Setup
CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  dept_id INT,
  dept_name VARCHAR(50)
);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, name, dept_id, dept_name)
VALUES
  (1, 'Ada', 1, NULL),
  (2, 'Bob', 2, NULL),
  (3, 'Carol', 1, NULL);
Validated query
SQL
UPDATE employees e
INNER JOIN departments d ON e.dept_id = d.id
SET
  e.dept_name = d.name;

SELECT
  id,
  name,
  dept_name
FROM
  employees
ORDER BY
  id;
Expected result
idnamedept_name
1AdaEngineering
2BobFinance
3CarolEngineering
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  dept_id INT,
  dept_name VARCHAR(50)
);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, name, dept_id, dept_name)
VALUES
  (1, 'Ada', 1, NULL),
  (2, 'Bob', 2, NULL),
  (3, 'Carol', 1, NULL);
Validated query
SQL
UPDATE e
SET
  e.dept_name = d.name
FROM
  employees e
  INNER JOIN departments d ON e.dept_id = d.id;

SELECT
  id,
  name,
  dept_name
FROM
  employees
ORDER BY
  id;
Expected result
idnamedept_name
1AdaEngineering
2BobFinance
3CarolEngineering
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  dept_id INT,
  dept_name VARCHAR(50)
);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, name, dept_id, dept_name)
VALUES
  (1, 'Ada', 1, NULL),
  (2, 'Bob', 2, NULL),
  (3, 'Carol', 1, NULL);
Validated query
SQL
UPDATE employees
SET
  dept_name = d.name
FROM
  departments d
WHERE
  employees.dept_id = d.id;

SELECT
  id,
  name,
  dept_name
FROM
  employees
ORDER BY
  id;
Expected result
idnamedept_name
1AdaEngineering
2BobFinance
3CarolEngineering
SQLite
Engine-specific syntax
Source table data
Setup
CREATE TABLE departments (id INT, name VARCHAR(50));

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  dept_id INT,
  dept_name VARCHAR(50)
);

INSERT INTO
  departments (id, name)
VALUES
  (1, 'Engineering'),
  (2, 'Finance');

INSERT INTO
  employees (id, name, dept_id, dept_name)
VALUES
  (1, 'Ada', 1, NULL),
  (2, 'Bob', 2, NULL),
  (3, 'Carol', 1, NULL);
Validated query
SQL
UPDATE employees
SET
  dept_name = (
    SELECT
      name
    FROM
      departments
    WHERE
      departments.id = employees.dept_id
  );

SELECT
  id,
  name,
  dept_name
FROM
  employees
ORDER BY
  id;
Expected result
idnamedept_name
1AdaEngineering
2BobFinance
3CarolEngineering

MySQL and MariaDB use UPDATE … JOIN … SET. PostgreSQL and SQL Server use UPDATE … SET … FROM. The resulting data is identical.

Useful when

Where this command helps.

  • denormalizing a lookup value into a row for reporting or caching
  • copying a value from a parent table into child rows after a schema change

Explanation

What the command is doing.

Updating rows based on data in another table requires different syntax per engine. MySQL and MariaDB extend UPDATE with a JOIN clause directly. PostgreSQL and SQL Server use a FROM clause to reference the second table. The effect is the same: rows in the target table are updated using matched values from the source.

Tags

updatejoindml

Related commands

Keep moving through nearby tasks.