UPDATE With a Join Needs Different Syntax on MySQL and PostgreSQL

Copying prices from a staging table with a join takes UPDATE ... FROM on SQLite and PostgreSQL and UPDATE ... JOIN on MySQL and MariaDB, and neither runs on all four.

The engines use different syntax for changing rows based on another table. DELETE differs the same way: DELETE ... USING on PostgreSQL, DELETE t FROM t JOIN ... on MySQL and MariaDB.

What Each Engine Answered

On a table made with

CREATE TABLE t (id INTEGER, x INTEGER);
CREATE TABLE u (id INTEGER, x INTEGER);
INSERT INTO t VALUES (1, 0), (2, 0);
INSERT INTO u VALUES (1, 7);

the example

UPDATE t SET x = u.x FROM u WHERE u.id = t.id;
SELECT x FROM t ORDER BY id;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 4 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

This form answers the same on every engine:

UPDATE t SET x = (SELECT u.x FROM u WHERE u.id = t.id) WHERE EXISTS (SELECT 1 FROM u WHERE u.id = t.id);
SELECT x FROM t ORDER BY id;

Use subqueries, which all four accept: UPDATE t SET x = (SELECT u.x FROM u WHERE u.id = t.id) WHERE EXISTS (SELECT 1 FROM u WHERE u.id = t.id), and DELETE FROM t WHERE id IN (SELECT id FROM u).

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:5:22: error: UPDATE ... FROM works on SQLite and PostgreSQL; MySQL and MariaDB refuse it. [update-join]
    FROM
    Use subqueries, which all four accept: UPDATE t SET x = (SELECT u.x FROM u WHERE u.id = t.id) WHERE EXISTS (SELECT 1 FROM u WHERE u.id = t.id), and DELETE FROM t WHERE id IN (SELECT id FROM u).
example.sql:6:26: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    id
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.

2 problems: 1 error, 1 note.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules update-join prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.