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

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)
)

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)

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.