sql - 在 SQL 中查找重复项并更新它们？

Question

我正在尝试查找表中的所有重复项并更改其中一个值。现在我使用:

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

只返回的问题

Amount
23.6500
41.8800
42.3500

而不是

Amount
23.6500
23.6500
41.8800
41.8800
42.3500
42.3500

所以我无法更新所有行。

我怎样才能像我展示的那样得到它？

谢谢，丹

Answer 1

只需将它包装在一个 IN 查询中:

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

UPDATE:添加了 UPDATE 语句

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