0

I have a table that contains

id  username    password    last_touch

It is possible to have duplicate entries for the same id. The last_touch is the timestamp of the last insert command. How can I delete all entries with the same id, apart from the last one that is left so I always have the user details which are most up to date?

Somewhere along the lines of:

DELETE FROM user_data 
WHERE id=1 
LIMIT (count(SELECT 1 
             FROM user_data 
             WHERE id=1) - 1)

(Obviously the syntax is not correct in the above example, so MySQL complains.)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Frank Vilea
  • 8,323
  • 20
  • 65
  • 86
  • It depends on the declaration of id, is it auto incremented (which it should be) or do you add values to id yourself? If it is auto incremented I don't think you can have several entires with the same id. – Griffin Oct 21 '11 at 15:15

2 Answers2

4

Use a nested query to select the latest timestamp for the given user id, and delete all rows for that id with timestamps different than that one:

DELETE FROM user_data
WHERE ID = 1 AND last_touch != 
    (SELECT latest FROM
        (SELECT MAX(last_touch) AS latest FROM user_data WHERE ID = 1) AS temp
    )

Update: Fixed direct reference to table being modified in inner SELECT by wrapping another SELECT around it, as per Frank's comments.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • Hmm.. tried it, but I get "#1093 - You can't specify target table 'users' for update in FROM clause" – Frank Vilea Oct 21 '11 at 17:03
  • I think this is the problem: http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause – Frank Vilea Oct 21 '11 at 17:07
  • @FrankVilea: Indeed, I had forgotten about that restriction. Updated the answer with a working query. – Jon Oct 21 '11 at 17:26
2
DELETE FROM user_data u
WHERE id=1 
AND last_touch < (SELECT max(last_touch)
                  FROM user_data 
                  WHERE id=u.id)
StevieG
  • 8,639
  • 23
  • 31