0

I want to delete all duplicate records from my table except one record (tbl_user_points)

Table Data 

id  userid  points  Reason
------------------------------
132 1278    50  Bonus points
153 1278    50  Bonus points
174 1278    50  Bonus points
195 1278    50  Bonus points

So my final output will be

id  userid  points  Reason
------------------------------
132 1278    50  Bonus points

Please help me on this

suspectus
  • 16,548
  • 8
  • 49
  • 57
user3101582
  • 57
  • 1
  • 1
  • 8

3 Answers3

2

Try this

   Delete
       t1
    FROM 
       tTable t1, tTable t2 
    WHERE 
       t1.userid  = t2.userid  AND 
       t1.points  = t2.points  AND 
       t1.Reason  = t2.Reason AND 
       t1.id < t2.id

(Or)

This will always leave the row with the lowest ID -

  DELETE t2
    FROM `table` t1
    INNER JOIN `table` t2
        ON t1.userid  = t2.userid  AND 
           t1.points  = t2.points  AND 
           t1.Reason  = t2.Reason AND 
           t1.id < t2.id
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

Try:

DELETE FROM tbl_user_points
WHERE id NOT IN (
    SELECT id FROM tbl_user_points GROUP BY userid, points, Reason
)
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • I was going to post something that involves PHP manipulation before it passes the SQL statement against the database, but then you answered with this post, pure SQL. I didn't try this but it makes a lot of sense judging by the syntax. Thank you Sir, I learned something from this. – chris_techno25 Jan 18 '14 at 10:30
  • MySql don't allow to Same table_name in target and sub-query. got this error after executing the query `Error Code: 1093. You can't specify target table 'tbl_user_points' for update in FROM clause.` – Yograj Sudewad Jan 18 '14 at 11:56
0

Please try this.

DELETE FROM tbl_user_points
    WHERE id NOT IN (
        SELECT * FROM(SELECT id FROM tbl_user_points1 GROUP BY userid, points, reason
    ) AS p)
Yograj Sudewad
  • 343
  • 2
  • 9