Using SQL to Update a Counter Each Time a Field Value Changes (i.e increment count of records in a group)

I have a table that contains some data that has a count of the number times that same MATERIAL & PLANT combination appears. For example purposes, the table is called 'TABLE' and has the following fields and data.

MATERIAL, PLANT, COUNT_MAT_PLANT
10001, WE01, 1
10001, WE01, 2
10001, WE02, 1
10001, WE02, 2
10002, WE01, 1
10002, WE01, 2
10002, WE04, 1

I need some SQL that will update a new field called COUNT_MATERIAL_OCC with an incrementing number based upon how many times the MATERIAL appears in the data. I want the counter to increment based upon the sort order of data in this seuence MATERIAL, PLANT, COUNT_MAT_PLANT. The resulting output should look like this:

MATERIAL, PLANT, COUNT_MAT_PLANT, COUNT_MATERIAL
10001, WE01, 1, 1
10001, WE01, 2, 2
10001, WE02, 1, 3
10001, WE02, 2, 4
10002, WE01, 1, 1
10002, WE01, 2, 2
10002, WE04, 1, 3

Any help would be greatly appreciated.
thumbcastAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
That could be a (very) slow, depending on the size of the table and the index(es) available.
0
 
brejkCommented:
Solution for SQL Server 2005 is in the snippet.
;with cte as (
  select 
    METERIAL, 
    PLANT, 
    COUNT_MAT_PLANT, 
    ROW_NUMBER() OVER (PARTITION BY MATERIAL ORDER BY PLANT, COUNT_MAT_PLANT) AS rn
  from [TABLE]
)
update t
set t.COUNT_MATERIAL = c.rn
from [TABLE] t
inner join cte c
on t.MATERIAL = c.MATERIAL 
and t.PLANT = c.PLANT 
and t.COUNT_MAT_PLANT = c.COUNT_MAT_PLANT

Open in new window

0
 
thumbcastAuthor Commented:
Thanks for the assistance, however, I just tried creating a storer procedure with the code above, however, I get the follwoing errror

"ERROR 156: Incorrect Syntax near the keyword 'With'. ROW_NUMBER is not a recognized function name"
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
brejkCommented:
My solution was for SQL Server 2005. You are probably using some earlier version.
0
 
thumbcastAuthor Commented:
I am using Microsoft  SQL Server 2000
0
 
Scott PletcherSenior DBACommented:
UPDATE t1
SET COUNT_MATERIAL = (
    SELECT COUNT(*)
    FROM tablename t2
    WHERE t2.MATERIAL = t1.MATERIAL
    AND (t2.PLANT < t1.PLANT OR
            (t2.PLANT = t1.PLANT AND t2.COUNT_MAT_PLANT <= t1.COUNT_MAT_PLANT))
    )
FROM tablename t1
0
 
thumbcastAuthor Commented:
Thank you Scott - that worked a treat. I only have a couple of thousand records to update, so the solution works perfectly.

Andy
0
All Courses

From novice to tech pro — start learning today.