How do I update last row of a group in MySQL

Question

I have a table like this:

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

I would like to update the current column such that only each user's most recent entry has a 1.

I had planned:

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

But this tells me ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Is there a way around this limitation of MySQL?

Answer 1

Yes, there is a workaround, using JOIN to a derived table:

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 ;

---

If (user, date) is not enough to guarantee uniqueness and there is some other id column that is either unique or primary key (or the (user, date, id) is unique, that's enough), you can do this to keep only one row per user with 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 ;

Answer 2

or you can write it like this

        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] )