CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 432

sql server update duplicate rows in the same table

I hope i can explain my problem very well.
I have a table without a primary key. To get unique row i have to select distinct (make, model, category,onhand) together and the result is 2 rows for each one with onhand is null and the other onhand is not null.

Now what I want to do is update my table to set null onhand = onhand with value for each duplicated row
I had this query to find the duplicates-2 for each

select  distinct MAKE, MODEL, category, PRGR,
CountDuplicateRows= COUNT(1)    --column to count the number of duplicates
from [OITM2]
WHERE PRGR !='p'
GROUP BY  MAKE, MODEL, category, PRGR
HAVING COUNT(1)>1   --more than one record
ORDER BY COUNT(1) DESC  --sort by most duplicates

But i can't figure out how to update the onhand null. i'm using sql server 2008 r2.
thank you

Upvotes: 0

Views: 3871

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

SQL Server has the very nice feature of updatable CTEs and subqueries. You can do:

with toupdate as (
      select t.*,
             count(*) over (partition by  MAKE, MODEL, category, PRGR) as cnt
      from oitm2
      where prgr <> 'p'
     )
update toupdate
    set onhand = YOURVALUEGOESHERE
    where cnt > 1 and onhand is null;

Notice that the subquery is not using aggregation, but instead using the count() window function. This appends the count to each row of the original data -- and it can still be used for updating.

If you want to get an arbitrary value from the same group of rows, you can add that into toupdate:

with toupdate as (
      select t.*,
             count(*) over (partition by  MAKE, MODEL, category, PRGR) as cnt,
             max(onhand) over (partition by  MAKE, MODEL, category, PRGR) as onhand_value
      from oitm2
      where prgr <> 'p'
     )
update toupdate
    set onhand = onhand_value
    where cnt > 1 and onhand is null;

Upvotes: 2

Related Questions