Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: find duplicate and update just one of them

    Can somebody please give me an idea on how to do this...

    I must look for duplicate records in a certain table then i must update one of the records

    ex. I found a duplicate of this record:

    ID PRODUCT AMOUNT
    ----------------------
    1 computer $5000
    2 computer $5000

    If I found one, i must update one of them, just one of them.

    So, how can I do that?

  2. #2
    Join Date
    Nov 2008
    Posts
    1

    Question Is Ther pseudo field as Row_num in oracle ?

    update filed1 = value1 where condition= condition1 and row_num =1

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Which of the dupes do you want to update?
    We don't do random, we do rules.

    P.S. what version of SQL Server?
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by marar.satheesh
    update filed1 = value1 where condition= condition1 and row_num =1
    That could work but I'm not just looking for 1 pair of duplicate records.

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by georgev
    Which of the dupes do you want to update?
    We don't do random, we do rules.

    P.S. what version of SQL Server?
    I'm using SQL Server 2000. So I think that's version 7, right?

    Actually, either of them will do since they're just the same.

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    Version 8.0 is SQL Server 2000.
    Code:
    DECLARE	@Sample TABLE
    	(
    		ID INT,
    		Product VARCHAR(20),
    		Amount MONEY
    	)
    
    INSERT	@Sample
    SELECT	1, 'Computer', 5000 UNION ALL
    SELECT	2, 'Computer', 5000
    
    SELECT	*
    FROM	@Sample
    
    UPDATE		s
    SET		s.Amount = 0
    FROM		@Sample AS s
    LEFT JOIN	(
    			SELECT		MIN(ID) AS minID
    			FROM		@Sample
    			GROUP BY	Product,
    					Amount
    		) AS x ON x.minID = s.ID
    WHERE		x.minID IS NULL
    
    SELECT	*
    FROM	@Sample

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE dbo.products (
       id      int
     , product char(10)
     , amount  money
    )
    
    --Sample data
    INSERT INTO dbo.products (id, product, amount)
          SELECT  1, 'computer', 5000
    UNION SELECT  2, 'computer', 5000
    UNION SELECT  3, 'mouse   ', 88800
    UNION SELECT  4, 'sawdust ', 0.01
    UNION SELECT  5, 'bananas ', 100
    UNION SELECT  6, 'bananas ', 110
    UNION SELECT  7, 'bananas ', 100
    UNION SELECT  8, 'monkey  ', 11
    UNION SELECT  9, 'monkey  ', 11
    UNION SELECT 10, 'monkey  ', 11
    
    SELECT * FROM dbo.products
    
    UPDATE dbo.products
    SET    amount = 9999.99
    FROM   dbo.products p
     INNER
      JOIN (
            SELECT Min(id) As [min_id]
                 , product
                 , amount
            FROM   dbo.products
            GROUP
                BY product
                 , amount
            HAVING Count(*) > 1
           ) As [dupes]
        ON p.id = dupes.min_id
    
    SELECT * FROM dbo.products
    
    GO
    DROP TABLE dbo.products
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ach, sniped by Peso!
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2007
    Posts
    183
    Not really. I update all but one records when duplicate is found.
    You update just one of the duplicates.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    True, but there was a reason for that
    Quote Originally Posted by BlueGemini
    If I found one, i must update one of them, just one of them.
    Blue: if you want to update a single duplicate then use inner join, if you want to update all but one, then left join with null test will do it for you.
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    BlueGemini,

    Out of curiosity, is this an academic question or real-world?

    If real world, what you tell us what the origin/purpose is of this situation?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Obviously, when you're done you're going to apply the necessary unique constraint too, right?
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by Wim
    BlueGemini,

    Out of curiosity, is this an academic question or real-world?

    If real world, what you tell us what the origin/purpose is of this situation?
    Its actually a real-world thing. I'm given a task to process an excel file with a number of records to change the AMOUNT field to 0 for records that has a duplicates. Since doing it manually is tedious especially if you're working with lots of records

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Obviously, when you're done you're going to apply the necessary unique constraint too, right?
    Unique constraint in Excel?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You'll note that my post was prior to the admittance of Excel
    George
    Home | Blog

Posting Permissions

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