MySQL UPDATE ... SELECT 實戰模式：JOIN 更新、臨時表繞過限制與子查詢賦值

MySQL 不支援標準 SQL 的 UPDATE ... FROM ... 語法，且禁止在 UPDATE 子查詢中直接引用目標表。本文匯總 JOIN 更新（首選）、**臨時表包裹法（繞過同表限制）**、子查詢賦值三大核心模式，附帶實戰代碼與性能建議。

一、 核心限制回顧

• ❌ UPDATE t SET col = (SELECT ... FROM t WHERE ...) — ERROR 1093: You can't specify target table 't' for update in FROM clause
• ❌ UPDATE t1, t2 SET t1.col = t2.col WHERE ... — 舊式逗號連接雖支援，但語義不清，不推薦。

二、 模式一：JOIN 更新（標準、高效、推薦）

基本語法

UPDATE target_table t
INNER JOIN source_table s ON t.join_key = s.join_key
SET t.target_col = s.source_col
[WHERE ...];

實戰範例 1：同步好友表用戶名

UPDATE friends f
INNER JOIN users u ON f.friendid = u.userid
SET f.friendname = u.username;

實戰範例 2：多表關聯更新價格

UPDATE product p
INNER JOIN productPrice pp ON p.productId = pp.productId
SET pp.price = pp.price * 0.8, p.dateUpdate = CURDATE()
WHERE p.dateCreated < '2004-01-01';

三、 模式二：臨時表包裹法（繞過同表更新限制）

需求：根據同表中其他行的值更新當前行。

經典模式

UPDATE apples a
SET a.price = (
    SELECT price FROM (
        SELECT * FROM apples
    ) AS tmp
    WHERE variety = 'gala'
)
WHERE a.variety = 'fuji';

原理解析

1. 內層 SELECT * FROM apples 產生**臨時表** tmp（物化子查詢）。
2. 外層子查詢引用臨時表 tmp，不再直接引用目標表 apples。
3. 滿足優化器「目標表不出現在 FROM 子句」的檢查。

更多實戰變體

-- 變體 1：別名簡寫
UPDATE a 
SET a.xx = (
    SELECT yy FROM (
        SELECT yy FROM b
    ) c 
    WHERE a.id = b.id  -- 注意：此處 b 未定義，實際需在臨時表中關聯
);

-- 正確寫法：臨時表帶出關聯鍵
UPDATE a
INNER JOIN (
    SELECT id, yy FROM b
) c ON a.id = c.id
SET a.xx = c.yy;  -- 推薦：直接用 JOIN 更新，更清晰高效

-- 經典嵌套賦值
UPDATE mem_player 
SET DataWarehouse = (
    SELECT DataWarehouse FROM (
        SELECT * FROM mem_player
    ) AS b 
    WHERE Pid = 100000
);

四、 模式三：子查詢賦值（單值查找、配置同步）

UPDATE customers a
SET city_name = COALESCE((
    SELECT b.city_name 
    FROM tmp_cust_city b 
    WHERE b.customer_id = a.customer_id
), '未知');

• 子查詢**必須返回單行單列**，否則報錯 ERROR 1242: Subquery returns more than 1 row。
• 使用 COALESCE/IFNULL/NVL 提供默認值，防止 NULL 覆蓋原值。

五、 性能對比與選型建議

六、 進階技巧：CTE 簡化複雜更新 (MySQL 8.0+)

WITH source_data AS (
    SELECT id, new_value FROM external_source WHERE valid = 1
)
UPDATE target t
JOIN source_data s ON t.id = s.id
SET t.value = s.new_value;

CTE 替代臨時表，可讀性更強，優化器可直接下推條件。

七、 總結

MySQL UPDATE ... SELECT 核心在於：**JOIN 更新為主流，臨時表包裹解同表限制，子查詢賦值做單值同步**。掌握三大模式，配合 EXPLAIN 驗證執行計劃、關鍵列加索引、大表分批提交，即可高效應對各類業務數據同步場景。