Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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 ;
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
1  
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
add comment

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
add comment

Your Answer

 
discard

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.