Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: Delete duplicate records

    Hi All,
    How can i delete all the duplicate rows from a sybase table in one single query ?
    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Is there a unique identifier stored with every record by which you can separate the rows?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34
    yes,
    Lets assumption the table t1 with
    id int not null, name varchar(50) null, age smallint null

    id name age
    1 Amit 25
    2 Mayur 26
    3 Kunal 25
    4 Amit 25
    5 Ripal 27
    6 Mayur 26
    7 Amit 25
    8 Ripal 27

    I wanted to delete duplicate records with respect to name and age keeping one copy of records in the original table.
    after deletion or duplicate rocords; table containts :
    id name age
    1 Amit 25
    2 Mayur 26
    3 Kunal 25
    5 Ripal 27

  4. #4
    Join Date
    May 2011
    Location
    Pune(India)
    Posts
    32

    To delete all the duplicate rows

    You can use,

    delete from tableName where sid not in (select MAX(sid) from tableName group by DuplicateColumn1, DuplicateColumn2)

    eg.

    1> select * from sample
    2> go
    sid sname age
    ----------- -------------------- -----------
    1 AA 10
    5 AA 10
    2 BB 22
    4 BB 22
    3 CC 14
    6 CC 14

    (6 rows affected)
    1> select * from sample
    2> go
    sid sname age
    ----------- -------------------- -----------
    1 AA 10
    5 AA 10
    2 BB 22
    4 BB 22
    3 CC 14
    6 CC 14

    (6 rows affected)
    1> delete from sample where sid not in (select MAX(sid) from sample group by sname,age)
    2> go
    (3 rows affected)
    1> select * from sample
    2> go
    sid sname age
    ----------- -------------------- -----------
    5 AA 10
    4 BB 22
    6 CC 14

    (3 rows affected)
    --
    Thanks
    Gopal Pawar

  5. #5
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34
    Thanks Gopal !!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •