MySQL 不支援標準 SQL 的 UPDATE ... FROM 語法,也無法直接在 SET 子句中使用會回傳多列的子查詢。本文整理三種主流解法,並說明避坑重點。
-- 將 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.col2MySQL 禁止在 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。
-- 錯誤寫法(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
);
friendid、userid、id 等 JOIN 條件欄位必須建立索引。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)不會物質化為臨時表(視優化器決定),語意更清晰,建議新專案採用。