deleting duplicate rows from sql server databse

Discussion in 'Databases' started by patidarvijay, Apr 30, 2011.

0
  1. #1
    Hi

    i am facing an issue i have a sql server database with a table named products this table having 1000 records and each record gave a unique id but the other attributes are all same for some rows.

    here i want to delete that rows which are duplicate.

    how can i do this?
     
    patidarvijay, Apr 30, 2011 IP
  2. kai555

    kai555 Peon

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    kai555, May 3, 2011 IP
  3. aman_gcs

    aman_gcs Member

    Messages:
    178
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    26
    #3
    SELECT MAX(ID)
    FROM MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
     
    aman_gcs, May 3, 2011 IP
  4. randheer

    randheer Greenhorn

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    selecting max id and putting this sub query in delete statement will work...

    delete * from table where id not in (select max id sub query)
     
    randheer, May 6, 2011 IP
  5. m0nster

    m0nster Peon

    Messages:
    30
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    if you set ur table to UNIQUE you shouldn't be getting duplicate entries, check ur tables and make sure u only have 1 set to UNIQUE, i had this problem a while ago and after i remade the table with only one field set to UNIQUE it fixed the problem
     
    m0nster, Jun 2, 2011 IP