sql query which will update the record with incremental values

have a temptable as follows

 1. id  empid  triggerstatus
     1   2881     null 
     2   2881     null
     3   2881     null
     4   2882     null

i need a sql query which will update the triggerstatus dynamically to triggerstatus+1 if the empid is repected twice,trice,etc.

my result should look as

id  empid  triggerstatus
1   2881     1
2   2881     2
3   2881     3
4   2882     1

Answers


If you have SQL Server 2005+ you can use a CTE and ROW_NUMBER Windowing function:

WITH cte AS
(
   SELECT id, empId, triggerStatus
      , ROW_NUMBER() OVER (PARTITION BY empId ORDER BY id ASC) AS RowNumber
   FROM yourTable
)
UPDATE t
SET triggerStatus = RowNumber
FROM yourTable AS t
INNER JOIN cte ON t.id = cte.id

You may want to add a WHERE clause if there are some records that you do not want to update triggerStatus for.


Need Your Help

How to extract two rgb values/strings from the another string using regex or another method

javascript jquery html regex

The following string has been retrieved from an element's style attribute, the attribute is a variable and can contain different colors in a particular vertical gradient background:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.