各方言里带连接的 UPDATE 和 DELETE

根据另一张表执行 UPDATE 或 DELETE 时，各引擎的语法差异很大。先看通用的子查询写法，再比较使用连接的写法。

这一页会修改数据，改的是你浏览器里那份自己的示例数据库。想重来就从欢迎页重新载入示例。

到处都能跑的写法

UPDATE 的 WHERE 就是普通的 WHERE，所以可以用子查询挑出要动的行。没有哪个引擎在这件事上需要特别的语法。

-- Works on every engine in this list, unchanged.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded' AND total > 2000
);

在 Planche 中运行

242 行被修改

浏览器会另开一个标签页打开 Planche，示例数据库已经载入，这条查询也已经放好。本页其他示例都会复用同一个标签页；在你按下 F9 之前，什么都不会执行。

当新值本身来自另一张表时，SET 里的相关子查询也能做到：

-- The same shape when the new value comes from the other table.
UPDATE products
SET stock_quantity = stock_quantity + (
  SELECT COALESCE(SUM(change_qty), 0)
  FROM inventory_logs il
  WHERE il.product_id = products.id
    AND il.reason = 'return'
)
WHERE id IN (SELECT product_id FROM inventory_logs WHERE reason = 'return');

在 Planche 中运行

203 行被修改

注意那个重复出现的条件。SET 里的子查询决定值是多少，WHERE 里的那个决定到底动哪些行——没有后者，所有商品都会被更新，而那些没有对应日志的商品会被 COALESCE(..., 0) 设成原样：什么也没加，行却照写不误。

这种写法可移植，也好读。但它不总是最快的：引擎可能对每一行都跑一次相关子查询，执行计划会告诉你有没有这么干。下面这些连接语法之所以存在，是因为在大批量更新时，那点差别值得拿可移植性去换。

MySQL 和 MariaDB

MySQL / MariaDB

-- The join goes between UPDATE and SET.
UPDATE customers c
  JOIN orders o ON o.customer_id = c.id
SET c.loyalty_points = c.loyalty_points + 10
WHERE o.status = 'refunded';

-- DELETE names which table's rows to remove, before FROM.
DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

MySQL / MariaDB — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

连接夹在 UPDATE 和 SET 之间，这和这里的其他引擎都不一样。DELETE 则要求在 FROM 之前点名目标——DELETE c FROM customers c JOIN ...——因为两张表连在一起时，语句本身说不清要删谁的行。漏掉那个别名会是语法错误，这还算好的；写成另一个表的别名就不会报错了。

PostgreSQL

-- The second table goes in FROM, and the join condition in WHERE.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded';

-- DELETE uses USING rather than FROM for the extra table.
DELETE FROM wishlists
USING products p
WHERE p.id = wishlists.product_id
  AND p.stock_quantity = 0;

PostgreSQL — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

多出来的那张表放在 FROM 里，连接条件挪到 WHERE 里。有两处很容易写错：目标表不要在 FROM 里重复（重复了就是和自己连接），以及 UPDATE 用 FROM，而 DELETE 用 USING。

SQLite 在 3.33（2020）引入了和 PostgreSQL 语义一致的 UPDATE … FROM，所以本页第三个例子在这里是能跑的：

-- SQLite 3.33 (2020) and later, with PostgreSQL's spelling.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded'
  AND o.total > 2500;

在 Planche 中运行

224 行被修改

SQLite 没有 DELETE … USING，所以删除仍然走子查询。

SQL Server

-- The alias is updated, and the FROM clause repeats the target table.
UPDATE c
SET c.loyalty_points = c.loyalty_points + 10
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

SQL Server — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

被更新的是那个别名，而 FROM 再写一遍目标表，好给这个别名一个定义。读起来很怪——一条语句里表名出现了两次——但 T-SQL 只接受这种形式，DELETE 也遵循同样的写法。

Oracle

-- Oracle has no UPDATE ... FROM and no UPDATE ... JOIN.
-- EXISTS is the portable answer, and the one to use.
UPDATE customers c
SET c.loyalty_points = c.loyalty_points + 10
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'refunded'
);

DELETE FROM wishlists w
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.id = w.product_id AND p.stock_quantity = 0
);

Oracle — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

Oracle 既没有 UPDATE … JOIN 也没有 UPDATE … FROM。用相关子查询配 EXISTS 是常规答案，而且这条语句在另外四个引擎上原样能跑。

还有第二个选择，可更新的连接视图——UPDATE (SELECT a.col, b.col FROM a JOIN b ON …) SET …——但 Oracle 只在能证明连接保键时才接受它，这要求被连接的列上有唯一约束或主键。证明不了时它会抛 ORA-01779，解决办法就是退回 EXISTS。

运行它们之前

正是这类语句，让 Planche 在执行前把 UPDATE 和 DELETE 改写成对应的 SELECT：有连接在，受影响的范围光靠读语句比普通 WHERE 难判断得多。打开设置 › 安全 › 预览受影响的行，你会先拿到条数和一份样本。这套改写认得上面全部五种形状，仓库里还有一个脚本，会在 MySQL、PostgreSQL、SQL Server、SQLite 和 Oracle 上核对推导出的条数和真实语句改动的行数是否一致。

跨两张表的 UPDATE 和 DELETE

到处都能跑的写法

MySQL 和 MariaDB

PostgreSQL

SQL Server

Oracle

运行它们之前