We help IT Professionals succeed at work.

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

thumbcast
thumbcast asked
on
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.
Comment
Watch Question

Commented:
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

Author

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"

Commented:
My solution was for SQL Server 2005. You are probably using some earlier version.

Author

Commented:
I am using Microsoft  SQL Server 2000
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
That could be a (very) slow, depending on the size of the table and the index(es) available.

Author

Commented:
Thank you Scott - that worked a treat. I only have a couple of thousand records to update, so the solution works perfectly.

Andy

Explore More ContentExplore courses, solutions, and other research materials related to this topic.