Page 1 of 5 123 ... LastLast
Results 1 to 15 of 66
  1. #1
    Join Date
    Jan 2009
    Posts
    47

    Red face Unanswered: No Solution???

    Hello,
    I have a table with more than 203676 records.
    There are more than 34499 duplicate records.
    I usethe following script to update duplicate records only. But it is taking long time execute.

    Is there any way to fast update?




    DECLARE @ID int
    DECLARE @Count int
    DECLARE @FName nvarchar(50)
    DECLARE @LName nvarchar(50)
    DECLARE @Email nvarchar(50)

    DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
    select min(id), fname,lname,email,count(*) from tblinfo
    GROUP by fname,lname,email
    Having count(*)>1 Order by min(id)

    OPEN dublicate_cursor

    FETCH NEXT FROM dublicate_cursor INTO @ID,@FName, @LName,@Email, @Count

    WHILE @@FETCH_STATUS = 0
    BEGIN


    UPDATE tblinfo SET DupStatus=1 WHERE ID=@ID

    FETCH NEXT FROM dublicate_cursor INTO @ID,@FName, @LName,@Email, @Count
    END

    CLOSE dublicate_cursor
    DEALLOCATE dublicate_cursor
    Last edited by rudba; 01-12-09 at 15:53.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, 203676 is not a huge amount of data. It's pretty small, actually.
    Second, you should not be doing this using a cursor. That is why it is running so slow.
    Third, do you realize that if you have a record duplicated more than two times, your code is only going to update one of the instances? Is that the business logic you want, or do you want it to flag all but the duplicate with the highest ID?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2009
    Posts
    47
    my server is live.....our clients are accessing dbserver so i do not want to slow the server as well as fast update.

    when i execute this script, i found thoes are dup data. The dup datas were inserted by mistake. i can not delete thoes records because it is link to other tables too. this is the main table so i am planning to update this table.

    select min(id), fname,lname,email,count(*) from tblinfo
    GROUP by fname,lname,email
    Having count(*)>1 Order by min(id)

    how to update thoes records without cursor??
    Last edited by rudba; 01-12-09 at 12:50.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2009
    Posts
    47
    SQL Server 2005

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    USE playdb
    
    DECLARE @fun_with_dupes table (
       id        int
     , fname     varchar(10)
     , lname     varchar(10)
     , email     varchar(10)
     , DupStatus bit DEFAULT 0
    )
    
    INSERT INTO @fun_with_dupes (id, fname, lname, email)
          SELECT 1, 'George', 'V', 'test'
    UNION SELECT 2, 'George', 'V', 'test'
    UNION SELECT 3, 'George', 'V', 'test'
    UNION SELECT 4, 'George', 'V', 'test'
    UNION SELECT 5, 'Brucey', 'L', ';)'
    UNION SELECT 6, 'Pootle', 'F', 'something'
    UNION SELECT 7, 'Pootle', 'F', 'something'
    
    SELECT *
    FROM   @fun_with_dupes
    
    ; WITH cte AS (
      SELECT id
           , DupStatus
           , Row_Number() OVER (PARTITION BY fname, lname, email ORDER BY id ASC) As [n]
      FROM   @fun_with_dupes
    )
    UPDATE cte
    SET    DupStatus = 1
    WHERE  n > 1
    
    SELECT *
    FROM   @fun_with_dupes
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2009
    Posts
    47
    Is there any method without inserting any table?

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes. Preventing the duplicates in the first place comes to mind.....

  10. #10
    Join Date
    Jan 2009
    Posts
    47
    How to do without using cursor? If i used cursor it goes each record where records are matching....thats way it is taking long time...

    no body knows facing this kind of problems??

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by rudba
    no body knows facing this kind of problems??
    sure, just read MCrowley's answer.

    Tip: search for "primary key" or "unique index"

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want help?
    Try answering the questions I asked.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2009
    Posts
    47
    Yes Blindman,
    These are my duplicate records. I have to updates these records only. I tried using cursor.....I have to update highest ID. Looking for quick solution.

    DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
    select min(id), fname,lname,email,count(*) from tblinfo
    GROUP by fname,lname,email
    Having count(*)>1 Order by min(id)

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    And what is the reluctance over using the temp table as given by GeorgeV?

  15. #15
    Join Date
    Jan 2009
    Posts
    47
    MCrowley, i think that solution is not good for my case because my server is live...our clients are accessing this server. we have more than 203676 data. if i inserting thoes data into temp table......server will be slow ...i don't want to complain from our client so i am sorry.

Posting Permissions

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