Set value +1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set value +1

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-28 : 09:56:12

I'm adding a temporary column (TempID) to an existing table (EVP_COC) which will allow me to juggle some data. The TempID column needs to be populated with data which references an existing column (EVPNO) which is used to identify rows of related data. Every row has an EVPNO entry (i.e., RCSO00EVP000001) and there are always three entries for EVPNO. For instance there are currently three rows identified by RCSO00EVP000001, three rows identified by RCSO00EVP000002, and so on. The Temp ID field needs to be populated with values 1, 2, or 3, based on the succession of dates in the DATE column and by the common EVPNO entries. So, for EVPNO RCSO00EVP000001, there are three date entries (11-2-2003, 2-11-2003, and another 11-2-2003. If i were to order by date and by EVPNO, the EVPNO rows for RCSO00EVP000001 would be ordered 2-11-2003, 11-2-2003, 11-2-2003. The TEMPID column would assign the values of 1, 2, and 3 for those same rows, in that order.
So my question is, what code would i use to update the currently NULL value of TempID so that it sets a value of 1, 2, or 3, based on the date value and how that corresponds to common EVPNO values?
Gheez, i'm sure there was a better, shorter way to explain this, but my brains not fully in gear yet this morning.
Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 10:22:39

do you mean this?


UPDATE t
SET TempID= Rn
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY EVPNO ORDER BY datefield) AS Rn,TempID
      FROM table)t

or this?


UPDATE t
SET TempID= Rn
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EVPNO,datefield) AS Rn,TempID
      FROM table)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-28 : 10:29:17

Interesting. The second solution is not what i want. The first solution is close, and i will tweak it accordingly to deal with duplicate dates in my rows.

Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 10:34:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources