March 26, 2009 at 4:50 am
i have two column in a table like following
col1 col2
00
05
010
015
10
15
110
115
now what my requirement is to check the interval of 5 in col2, if the sequence is not matched in col2 like this
col1 col2
00
05
011
015
10
13
111
14
then it update the col2 on interval of 5. like this
col1 col2
00
05
010
015
10
15
110
115
Any Ideas?
March 26, 2009 at 5:13 am
Tables have no order so the only way I can see of ordering you data is by Col1, Col2.
(ie 1,4 will come before 1,11)
If this is OK, then something like this should work:
UPDATE D
SET Col2 = RowId * 5 - 5
FROM
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS RowId
        ,Col1, Col2
    FROM YourTable
) D
WHERE RowId * 5 - 5 <> Col2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply