mysql - 从 MySQL 表中更新 x 行

Question

我有这张表:

表格___Units

|--------|------------|
| UNI_Id | UNI_Status |
|--------|------------|
|      1 |     active |
|      2 |     active |
|      3 |     active |
|      4 |     active |
|      5 |     active |
|      6 |     active |
|--------|------------|

仅当当前状态为 active 时，如何才能将最后创建的两行(因此最高 UNI_Id)的状态更新为 inactive。

这是我尝试过的:

UPDATE ___Units SET UNI_Status='inactive' WHERE (
    SELECT * FROM ___Units ORDER BY UNI_Id DESC LIMIT 2
) sub
ORDER BY UNI_Id ASC

谢谢。

Answer 1

加入返回两个最高 ID 的子查询。

UPDATE ___Units AS u1
JOIN (
    SELECT UNI_Id
    FROM ___Units
    ORDER BY UNI_Id DESC
    LIMIT 2) AS u2 ON u1.UNI_Id = u2.UNI_Id
SET u1.UNI_Status = 'inactive'
WHERE u1.UNI_Status = 'active'