Results 1 to 16 of 16

Thread: Delete duplicate record

  1. #1
    Junior Member
    Join Date
    Apr 2007
    Answers
    2

    Delete duplicate record

    How can delete duplicate value from a table

    Raghvendra rai


  2. #2
    Junior Member
    Join Date
    Feb 2007
    Answers
    1

    Re: Delete duplicate record

    DELETE FROM our_table
    WHERE rowid not in
    (SELECT MIN(rowid)
    FROM our_table
    GROUP BY column1, column2, column3... ;


  3. #3
    Junior Member
    Join Date
    Apr 2007
    Answers
    5

    Re: Delete duplicate record

    To delete duplicate record we use "distinct clause" select distinct(columname) from tablename.


  4. #4

    Re: Delete duplicate record

    --for ex.
    --if there is a table... like..

    Create table Xyz
    (
    x varchar(5),
    y varchar(5)
    );

    --and you have inserted following row 3 or 4 times..
    insert into xyz values('a','b')
    ---you can use following stored procedure...
    create procedure removeduplicate
    as
    begin
    Create table #Xyz
    (
    x varchar(5),
    y varchar(5)
    )
    insert into #xyz select distinct * from xyz;
    delete from xyz
    insert into xyz select * from #xyz;
    select * from xyz
    end


  5. #5
    Moderator
    Join Date
    Sep 2006
    Answers
    920

    Re: Delete duplicate record

    Quote Originally Posted by venkat_1984 View Post
    To delete duplicate record we use "distinct clause" select distinct(columname) from tablename.
    "distinct" is used to eliminate duplicate records for display purpose only,not to delete records from table.

    Last edited by jainbrijesh; 05-22-2007 at 07:29 AM.
    Regards,
    Brijesh Jain
    ---------------------------------------------------------
    Connect with me on Skype: jainbrijesh
    Google Plus : jainbrijeshji

  6. #6
    I am the DON
    Join Date
    Apr 2006
    Answers
    99

    Post How to Delete duplicate records from a table ???

    Quote Originally Posted by raghvendra_rai View Post
    How can delete duplicate value from a table

    Raghvendra rai
    Hi Friend,

    In order to delete the Duplicate records from a table ---

    DELETE from t1
    where rowid not in ( select max(rowid)
    from t2
    where t1.col1 = t2.col1
    and t1.col2 = t2.col2
    and t1.col3 = t2.col3)

    Here t1 and t2 are one and the same. Just we are getting the records from the same table by using sub-query.

    Sanghala

    If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

  7. #7
    Junior Member
    Join Date
    Apr 2007
    Answers
    5

    Re: Delete duplicate record

    Thanks for sending me this message. Tell me the syntax to create a user defined function.


  8. #8
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Delete duplicate record

    you shall get the syntax in google.
    I feel there is no need to post here for such things.


  9. #9
    Junior Member
    Join Date
    Jan 2007
    Answers
    6

    Re: Delete duplicate record

    Delete from where rowid not in(select min(rowid) from
    group by );


  10. #10
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Delete duplicate record

    Do we have rowid function in Sql Server??

    I think this is the right thread to ask this question since couple of you guys suggested to remove the duplicate records by using rowid function in sql server.


  11. #11
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Delete duplicate record

    Quote Originally Posted by jamesravid View Post
    Do we have rowid function in Sql Server??

    I think this is the right thread to ask this question since couple of you guys suggested to remove the duplicate records by using rowid function in sql server.
    Rowid function is invalid in SQL Server.
    Rowid is the best way to eliminate duplicate records in Oracle & in SQL Server, the distinct function can be used.

    *** Innila ***

  12. #12
    I am the DON
    Join Date
    Apr 2006
    Answers
    99

    Re: Delete duplicate record

    Quote Originally Posted by jamesravid View Post
    Do we have rowid function in Sql Server??

    I think this is the right thread to ask this question since couple of you guys suggested to remove the duplicate records by using rowid function in sql server.


    NO, we are not having rowid function in SQL server.

    Therefore the above query will not applicable to delete the Duplicates from a table in SQL server.

    Sanghala

    If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

  13. #13
    Junior Member
    Join Date
    Jul 2007
    Answers
    5

    Re: Delete duplicate record

    DELETE FROM tablename WHERE rowid not in(SELECT MIN(rowid)FROM tablename GROUP BY column1,column2 )


  14. #14
    Junior Member
    Join Date
    Jun 2007
    Answers
    5

    Re: Delete duplicate record

    Rowid is not there in Sql Server 2000.
    Rowid can be used in Oracle only.

    Do not get confused.

    We can delete the duplicate records by using having clause.


  15. #15
    Junior Member
    Join Date
    Sep 2005
    Answers
    1

    Re: Delete duplicate record

    DELETE FROM table_name
    WHERE ROWID NOT IN (SELECT max(ROWID)
    FROM table _name
    GROUP BY duplicate_values_field_name);


  16. #16
    Junior Member
    Join Date
    Mar 2008
    Answers
    1

    Re: Delete duplicate record

    Row ID function not in Sqlserver 2000 but sql server 2005 row id function is there


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact