CODEWITHSUNDEEP
sqlcountduplicates
Samter
Samter

Reputation: 131

Updating Duplicate Rows then deleting all except one

I am using SQL Server 2005 to try to find all the duplicates of a certain item and change the quantity so that it will equal the number of duplicate items. Then I plan to remove all the duplicate rows except one.

Here Is what I have so far:

UPDATE Table
SET Quantity = COUNT(Item)
WHERE COUNT(Item) > 1

SELECT * FROM Item

Any advice?

Upvotes: 2

Views: 546

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Here is one way to do what you want.

with toupdate as (
     select item, count(*) as cnt
     from t
     group by item
    )
update t
    set quantity = cnt
    from toupdate
    where t.item = toupdate.item

The CTE calculates the value for each item. The update clause then does the update.

Upvotes: 1

Related Questions