不能在 FROM 子句中指定更新的目標表

Question

我有一個簡單的 mysql 表：

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);

我嘗試執行以下更新，但只收到錯誤 1093：

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL
OR gehalt <
(SELECT (
    SELECT MAX(gehalt * 1.05)
    FROM pers MA
    WHERE MA.chefID = MA.chefID)
    AS _pers
))

我搜尋了錯誤並從mysql以下頁面找到了http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html，但這對我沒有幫助。

我該如何修正sql查詢？

Answer 1

您可以透過三個步驟完成此操作：

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

或

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

Answer 2

問題在於，無論出於何種愚蠢的原因，MySQL 不允許您編寫以下查詢：

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

也就是說，如果您要對錶執行UPDATE/INSERT/DELETE 操作，則無法在內部查詢（但您可以引用外部表中的欄位...）

---

解決方案是將子查詢中的myTable實例替換為(SELECT * FROM myTable)，如下所示

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

這顯然會導致必要的欄位被隱式複製到臨時表中，因此這是允許的。

我找到了這個解決方案這裡。該文章的註解：