PostgreSQL UPDATE 語法詳解：標準 SQL 相容與 FROM 子句擴充

PostgreSQL UPDATE 完整支援 SQL 標準，並提供強大的 FROM 子句擴充，支援多表聯結更新。本文詳細說明語法、參數、輸出、範例與相容性說明。

標準語法 (SQL 標準)

UPDATE table_name
SET column_name = expression [, ...]
[WHERE condition];

PostgreSQL 擴充：FROM 子句

UPDATE table_name
SET column_name = expression [, ...]
[FROM from_list]
[WHERE condition];

• from_list：可包含其他資料表、子查詢、函數呼叫，支援 JOIN。
• 允許在 SET 與 WHERE 中引用 FROM 列表中的欄位。
• 目標表 不可 出現在 FROM 列表中 (否則會建立自我聯結，需用別名區分)。

參數說明

輸出

UPDATE count

count 為受影響列數。0 表示無列符合條件。

範例

基本更新

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

FROM 子句聯結更新 (PostgreSQL 特有)

-- 依據另一表資料更新
UPDATE employees e
SET salary = e.salary * 1.1
FROM departments d
WHERE e.dept_id = d.dept_id
  AND d.name = 'Sales';

子查詢更新

UPDATE products p
SET price = (SELECT AVG(price) FROM products WHERE category = p.category)
WHERE price IS NULL;

陣列欄位更新

-- 更新單一元素
UPDATE sensors SET readings[3] = 100 WHERE id = 1;
-- 更新切片
UPDATE sensors SET readings[1:3] = ARRAY[10,20,30] WHERE id = 1;
-- 更新整個陣列
UPDATE sensors SET readings = ARRAY[1,2,3,4,5] WHERE id = 1;

相容性說明

最佳實務

1. 永遠加 WHERE，避免全表更新。
2. 大量更新分批 (CTE + LIMIT / WHERE ctid IN (...))。
3. 使用 RETURNING 子句取回更新後的值：UPDATE ... SET ... WHERE ... RETURNING id, col;。
4. 關鍵業務包裝在交易中 (BEGIN ... COMMIT)，設定 LOCK TABLE 避免併發衝突。

PostgreSQL 的 UPDATE ... FROM 是極具表達力的擴充，善用它能以單一陳述式完成複雜多表同步邏輯。