主页 / dba / 问题 / 35557
Accepted
artfulrobot
Asked: 2013-02-28 02:35:18 +0800 CST

如何在 MySQL 中更新组的最后一行

  • 772

我有一张这样的桌子：

+----------------------------+
| user  | date     | current |
+----------------------------+
|    1  | 20010101 |       1 |
|    1  | 20020202 |       1 |
|    1  | 20030303 |       1 |
|    2  | 20010101 |       1 |
|    2  | 20020202 |       1 |
+----------------------------+

我想更新当前列，以便只有每个用户的最新条目有 1。

我曾计划：

UPDATE table t1 SET current = 0 
WHERE EXISTS (
    SELECT id
      FROM table t2
     WHERE t2.user = t1.user
       AND t2.date > t1.date )
)

但这告诉我ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

有没有办法绕过 MySQL 的这个限制？

mysql

2 个回答

  1. Best Answer

    是的，有一种解决方法，JOIN用于派生表：

    UPDATE tableX AS t
  JOIN
    ( SELECT user, MAX(date) AS date
      FROM tableX
      GROUP BY user
    ) AS c
    ON  t.user = c.user
    AND t.date < c.date
SET 
    t.current = 0 ;

    如果(user, date)不足以保证唯一性并且还有一些其他id列是唯一的或主键（或者(user, date, id)是唯一的，这就足够了），您可以这样做以使每个用户只保留一行current=1：

    UPDATE 
    ( SELECT DISTINCT user_id
      FROM tableX 
    ) AS d
  JOIN
    tableX AS t
    ON  t.user_id = d.user_id 
    AND (t.date, t.id) <>
        ( SELECT i.date, i.id
          FROM tableX AS i
          WHERE i.user_id = d.user_id
          ORDER BY i.date DESC, i.id DESC
            LIMIT 1 
        )
SET 
    t.current = 0 ;
    • 3

  2. 或者你可以这样写

            UPDATE t1 SET t1.[current] = 0 
        FROM [table] T1
        WHERE EXISTS (
            SELECT 1
              FROM [table] t2
             WHERE t2.[user] = t1.[user]
               AND t2.[date] > t1.[date] )
    • 0

相关问题