MySQL Refuses an UPDATE Whose Subquery Reads the Same Table

Marking rows with UPDATE t SET flag = 1 WHERE id IN (SELECT id FROM t WHERE ...) works on SQLite, PostgreSQL and MariaDB. MySQL refuses an UPDATE or DELETE whose subquery reads the table being changed.

MySQL says you can’t specify target table 't' for update in FROM clause. MariaDB lifted the limit in version 10.3.2. A subquery used as a table works on MySQL too, because MySQL then computes its rows before it changes anything.

What Each Engine Answered

On a table made with

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

the example

UPDATE t SET x = 1 WHERE id IN (SELECT id FROM t WHERE x = 0);
SELECT x FROM t ORDER BY id;

gave these answers:

What to Write Instead

This form answers the same on every engine:

UPDATE t SET x = 1 WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE x = 0) AS s);
SELECT x FROM t ORDER BY id;

Wrap the subquery in one more SELECT used as a table: WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE x = 0) AS s).

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:43: error: MySQL refuses an UPDATE or DELETE whose subquery reads the table being changed; SQLite, PostgreSQL and MariaDB accept it. [update-same-table]
    FROM t
    Wrap the subquery in one more SELECT used as a table: WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE x = 0) AS s).
example.sql:4: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-same-table prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.