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.
Author |
Topic |
gscgis
Starting Member
6 Posts |
Posted - 2009-06-15 : 14:10:15
|
I have table containing data similar to below:MAPNUM UPDTDATE REVISIONNUMBER 1 01/10/08 1-011008-1 1 01/10/08 1-011008-2 1 01/10/08 1-011008-3 1 03/11/09 1-031109-1 1 03/11/09 1-031109-2 25 03/11/09 25-031109-1 31 04/16/09 1-041609-1 31 04/16/09 1-041609-2Basically my revision number is a unique id that uses a combo of the map number and update date and then an integer that makes this value unique if there is more than one revision in one map on the same date. So let's say if I have several revisions in map # 5 on 03/03/09, the revision number will be 5-030309-1, 5-030309-2, 5-030309-3 etc. etc.So what I would like to be able to do is update the revision number in one or two queries. I can easily append the map, - , date and - but now I need to increment in groups of combined map number and dates.I hope I have provided enough info.Thanks.G. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-15 : 14:17:16
|
You should be able to use the ROW_NUMBER() function. Something like:UPDATE TSET REVISIONNUMBER = <combine columns for RevNum> + CAST(RowNum AS VARCHAR(10))FROM( SELECT *, ROW_NUMBER() OVER (PARTITION BY MAPNUM ORDER BY UPDTDATE ASC) AS RowNum FROM MyTable) AS T |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-15 : 14:20:12
|
[code]UPDATE tSET t.REVISIONNUMBER=CAST(MAPNUM AS varchar(5)) + '-' + CONVERT(varchar(6),UPDTDATE,3) + '-' + CAST(Seq AS varchar(5))FROM(SELECT MAPNUM , UPDTDATE ,ROW_NUMBER() OVER(PARTITION BY MAPNUM,UPDTDATE ORDER BY UPDTDATE) AS Seq FROM YourTable)t[/code] |
|
|
gscgis
Starting Member
6 Posts |
Posted - 2009-06-15 : 14:46:11
|
Thanks to you both! Works perfectly. |
|
|
|
|
|