CODEWITHSUNDEEP
sqlsql-serverselectstored-proceduressql-update
Danpe
Danpe

Reputation: 19047

Find Duplicates in SQL and UPDATE them?

I'm trying to find all duplicates in a Table and change one of their values. Now i use:

SELECT Amount
FROM Bids
GROUP BY Amount, AuctionID
HAVING ( COUNT(Amount) > 1 ) AND  (AuctionID=1)

The problem that it returns only 

Amount
23.6500
41.8800
42.3500

And not

Amount
23.6500
23.6500
41.8800
41.8800
42.3500
42.3500

So I can't UPDATE all the rows.

How can I get it the way I showed?

Thanks, Dan

Upvotes: 3

Views: 6290

Answers (3)

Ben Thul
Ben Thul

Reputation: 32717

I'm curious to know why your original select doesn't satisfy your requirement. If for every member within a set of duplicates you're only selecting one of them, then you have one to update. It should be informative to add AuctionId to the select provided by Frank Schmitt to see what distinguishes these rows.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30815

Just wrap it inside an IN query:

SELECT Amount
FROM Bids
WHERE Amount IN (
  SELECT Amount
  FROM Bids
  GROUP BY Amount, AuctionID
  HAVING ( COUNT(Amount) > 1 ) AND  (AuctionID=1)
)

UPDATE: added UPDATE statement

UPDATE Bids
SET Burned = 1
WHERE Amount IN (
  SELECT Amount
  FROM Bids
  GROUP BY Amount, AuctionID
  HAVING ( COUNT(Amount) > 1 ) AND  (AuctionID=1)
)

Upvotes: 3

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

Assume that you have Id in Bids table: 

 SELECT Amount
 FROM Bids b1
 WHERE AcutionId = 1
 AND EXISTS (Select 1 from Bids b2 
             WHERE b2.AuctionID = b1.AuctionId 
             AND b1.Amount = b2.Amount
             AND b1.Id <> b2.Id)

Upvotes: 0

Related Questions