Results 1 to 3 of 3

Thread: query

  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Unanswered: query

    hi
    i have a doubt in sqlserver.
    how to delete the duplicate rows with a query in sqlserver
    thank you

  2. #2
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    One choice is to use Cursor.
    But remember that Cursors are very expensive to SQL Server.


    Else,

    You may try to use the UNION (not UNION ALL), with the same table. Put all the returned data into a temporary Table, then you delete your data from original table and insert the values from the temporary table into it.
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    First of all, what is structure of your table?
    If your table does not have a primary key - too bad... Use Diogo's advice and then check a normalization rules.
    If it does - you can try next method:

    drop table test
    go
    create table test (id int primary key,
    code varchar(10))
    go
    insert test values(1,'code1')
    insert test values(2,'code2')
    insert test values(3,'code1')
    insert test values(4,'code3')
    insert test values(5,'code4')
    insert test values(6,'code5')
    insert test values(7,'code6')
    insert test values(8,'code3')

    select *
    --delete
    from test
    where id in
    (select min(Id) from test group by code having count(*)>1)

    May be it needs to run last query couple times.....

Posting Permissions

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