萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL UPDATE 結合 SELECT 寫法全攻略:INNER JOIN 與子查詢實戰

MySQL 中 UPDATE 關聯查詢的正確姿勢

MySQL 不支援標準 SQL 的 UPDATE ... FROM 語法,也無法直接在 SET 子句中使用會回傳多列的子查詢。本文整理三種主流解法,並說明避坑重點。

方法一:INNER JOIN(推薦、效能最佳)

-- 將 friends.friendname 更新為 users.username
UPDATE friends
INNER JOIN users ON friends.friendid = users.userid
SET friends.friendname = users.username;
  • 可一次更新多欄位:SET a.col1 = b.col1, a.col2 = b.col2
  • 利用索引關聯,執行計畫穩定,適合大量資料。

方法二:子查詢 + 衍生表(繞過「不能更新同表」限制)

MySQL 禁止在 UPDATE 中直接引用同一張表的子查詢(錯誤 1093)。解法:用衍生表包裝子查詢,使其物質化為臨時表。

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

原理:內層 SELECT * FROM apples 強制建立臨時表 x,外層子查詢從 x 讀取,不再直接引用目標表 apples。

方法三:多表 UPDATE 的標準模板

-- 錯誤寫法(MySQL 不支援)
UPDATE a SET a.xx = (SELECT yy FROM b) WHERE a.id = b.id;

-- 正確寫法:INNER JOIN 衍生表
UPDATE a
INNER JOIN (SELECT yy FROM b) c ON a.id = b.id
SET a.xx = c.yy;

實戰範例:從同表複製欄位值

UPDATE mem_player
SET `DataWarehouse` = (
    SELECT `DataWarehouse` FROM (
        SELECT * FROM mem_player
    ) AS b
    WHERE `Pid` = 100000
);

效能與避坑提醒

  1. 優先用 INNER JOIN:MySQL 優化器對 JOIN 的處理成熟,通常比相關子查詢快。
  2. 確保關聯欄位有索引:friendid、userid、id 等 JOIN 條件欄位必須建立索引。
  3. 小心衍生表物質化成本:方法二的衍生表會建立臨時表,資料量大時可能拖慢效能;可考慮先建立實體暫存表再 JOIN。
  4. 避免「UPDATE 同表」直接子查詢:永遠用衍生表或 JOIN 繞過限制。

延伸:MySQL 8.0+ 的 CTE 寫法(更易讀)

WITH src AS (
    SELECT `DataWarehouse` FROM mem_player WHERE `Pid` = 100000
)
UPDATE mem_player t
JOIN src s ON 1=1
SET t.`DataWarehouse` = s.`DataWarehouse`;

CTE(Common Table Expression)不會物質化為臨時表(視優化器決定),語意更清晰,建議新專案採用。

copyright © 萬盛學電腦網 all rights reserved