Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 
    SELECT id
      FROM table t2
     WHERE t2.user = t1.user
       AND > )

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?

share|improve this question
up vote 3 down vote accepted

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

UPDATE tableX AS t
    ( SELECT user, MAX(date) AS date
      FROM tableX
      GROUP BY user
    ) AS c
    ON  t.user = c.user
    AND <
    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:

    ( SELECT DISTINCT user_id
      FROM tableX 
    ) AS d
    tableX AS t
    ON  t.user_id = d.user_id 
    AND (, <>
        ( SELECT,
          FROM tableX AS i
          WHERE i.user_id = d.user_id
            LIMIT 1 
    t.current = 0 ;
share|improve this answer
Thank you. This works well when there are not two records with the same MAX(date). Let's say that's valid data, and that another field is needed to determine the latest one, e.g. sort on date, ID - is there a solution for that too? (feel free to say this is a different question if not simple!) – artfulrobot Feb 27 '13 at 10:55
No, if you have a Primary or Unique key in the table, that would be a small change in the code. – ypercubeᵀᴹ Feb 27 '13 at 13:16
Thank you! That's perfect. – artfulrobot Feb 27 '13 at 14:09

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] )
share|improve this answer
Nope, this generates the same error. – artfulrobot Feb 27 '13 at 11:17
This works fine in SQL Sever.. Don't know if syntex is change in mySQL – Sandeep Kumar Feb 28 '13 at 3:59
The question is specifically about MySQL (hence title, tag), it is a known limitation of MySQL. Thanks anyway. – artfulrobot Feb 28 '13 at 11:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.