Post reply

check column consistency

  • March 26, 2009 at 4:50 am

    #384219

    i have two column in a table like following

    col1 col2

    0 0

    0 5

    0 10

    0 15

    1 0

    1 5

    1 10

    1 15

    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

    0 0

    0 5

    0 11

    0 15

    1 0

    1 3

    1 11

    1 4

    then it update the col2 on interval of 5. like this

    col1 col2

    0 0

    0 5

    0 10

    0 15

    1 0

    1 5

    1 10

    1 15

    Any Ideas?

  • March 26, 2009 at 5:13 am

    #966523

    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

    (

    &nbsp&nbsp&nbsp&nbspSELECT

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS RowId

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,Col1, Col2

    &nbsp&nbsp&nbsp&nbspFROM 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