MySQL UPDATE 和 JOIN 在同一个表上

Question

我知道这种问题已经被问过好几次了。解决方案是不直接使用第二个相同的表，而是使用 SELECT 作为虚拟表。我用虚拟表计算出了两个这样的查询。一个不起作用(A)，另一个起作用(B)，我不明白为什么(A)不起作用。

有人对此有解释吗？

CREATE TABLE tab (
id INT
, valid_from DATE
, valid_to   DATE
);

INSERT INTO TABLE tab (id,valid_from, valid_to)
VALUES
(1,'2000-01-01', NULL)
, (1,'2000-01-06', NULL)
, (1,'2000-01-20', NULL)
, (2,'2000-01-01', NULL)
, (2,'2000-01-10', NULL)
;

-- Case (A)
UPDATE tab a
SET a.valid_to = (SELECT MIN(b.valid_from) - INTERVAL 1 DAY AS valid_to
                  FROM (SELECT b1.it, b1.valid_from   -- virtual table
                        FROM tab b1 
                       ) AS b
              WHERE a.id = b.id
                        AND b.valid_from > a.valid_from
                  )
;
-- Output: "You can't specify target table 'a' for update in FROM clause

-- Case (B)    
UPDATE tab a
INNER JOIN (SELECT b1.id
                   , b1.valid_from
           , (SELECT MIN(b2.valid_from) - INTERVAL 1 DAY -- virtual table
                      FROM tab b2
                      WHERE b1.id = b2.id
                            AND b2.valid_from > b1.valid_from
                     ) AS valid_to
            FROM tab b1
        ) b
ON a.id = b.id
   AND a.valid_from = b.valid_from
SET a.valid_to = b.valid_to
;

结果:

 id  valid_from  valid_to   1   2000-01-01  2000-01-05   1   2000-01-06  2000-01-19   1   2000-01-20  \N    2   2000-01-01  2000-01-09   2   2000-01-10  \N

Answer 1

在 MYSQL 中不支持 case A 语法。最好只转到案例 B。

例如:update set语句不支持子查询。

在SQL server、oracle等中，它将支持case A和Case B语法。

我认为这些信息对您有用。

谢谢你..