Find Duplicates in SQL and UPDATE them?

Question

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

Answer 1

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