Modifying record for duplicate value

  • Hi,

    Having a bit of trouble with a script.

    I have a table such as :

    AppNo |AppDate

    ---------------------

    |200469151|27062005

    |200469151|27052005

    |200469151|21032006

    |200469160|21032006

    |200469160|27052005

    Here, as you can see the AppNo is the same for a few different AppDates. I would like to modify the AppNo by adding 10,000,000 to it for dates which are greater than the previous ones.

    So, I would like the end result to be:

    AppNo |AppDate

    ---------------------

    |300469151|27062005

    |200469151|27052005

    |400469151|21032006

    |300469160|21032006

    |200469160|27052005

    I tried to do stuff like:

    update @temp set AppNo = [AppNoMew] from

    (select a.AppNo+10000000 as 'AppNoNew', a.AppDate

    from @temp a, @temp b

    where a.AppDate > b.AppDate

    and a.AppNo = b.AppNo) a, @temp b

    where b.AppNo = a.AppNo

    and b.AppDate = a.AppDate

    But that didn't work.

    Am I trying to make it too complicated with the self-join and is there another easier way to get about it?

    Thanks

  • Here is some code that will do it for you - though I have used slow cursors so if it's a one off task then speed will not be an issue.

     

    create

    table app

    (

    AppNo

    INT

    , AppDate Datetime

    )

    INSERT

    app (AppNo, AppDate)

    values

    (

    200469151, '20050627')

    INSERT

    app (AppNo, AppDate)

    values

    (

    200469151, '20050527')

    INSERT

    app (AppNo, AppDate)

    values

    (

    200469151, '20060321')

    INSERT

    app (AppNo, AppDate)

    values

    (

    200469160, '20060321')

    INSERT

    app (AppNo, AppDate)

    values

    (

    200469160, '20050527')

    select

    * from app

    DECLARE

    @LoopCount INT

    , @LastAppNo INT

    SELECT

    @LoopCount

    = 0

    , @LastAppNo = 0

    DECLARE

    curApp CURSOR

    READ_ONLY

    FOR

    SELECT

    AppNo

    , AppDate

    FROM

    app

    ORDER BY

    AppNo

    ASC

    , AppDate ASC

    DECLARE

    @appno INT

    , @AppDate DateTime

    OPEN

    curApp

    FETCH

    NEXT FROM curApp INTO @appno, @AppDate

    WHILE

    (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    IF (@LastAppNo <> @appno)

    BEGIN

    SELECT

    @LoopCount

    = 1

    , @LastAppNo = @appno

    END

    IF (@LoopCount <> 1)

    BEGIN

    UPDATE

    a

    SET

    appno

    = appno + (100000000 * (@LoopCount - 1))

    FROM

    app a

    WHERE

    appno

    = @appno

    AND

    AppDate

    = @AppDate

    END

    SELECT

    @LoopCount

    = @LoopCount + 1

    END

    FETCH NEXT FROM curApp INTO @appno, @AppDate

    END

    CLOSE

    curApp

    DEALLOCATE

    curApp

     

    select

    * from app

     

    drop

    table app

    Excuse the formatting (or lack there of)

     


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • If this table is not too large, (upto maybe a 100k rows) - then you can use the following set based approach.

    create table test select (appNo int, appdate datetime)

    insert into test select 200469151, convert(datetime, '27/06/2005', 103)

    insert into test select 200469151, convert(datetime, '27/05/2005', 103)

    insert into test select 200469151, convert(datetime, '21/03/2006', 103)

    insert into test select 200469160, convert(datetime, '21/03/2006', 103)

    insert into test select 200469160, convert(datetime, '27/05/2005', 103)

    alter table test add prevappNo int

    update test set prevappNo = appNo

    select * from test order by prevappNo, appdate

    update test set

    appNo = appNo + 100000000 * (select count(*) from test t1

        where t1.prevappNo = t.prevappNo

        and t1.appdate < t.appdate)

    from test t

    select * from test order by prevappNo, appdate

    When you are done, you can drop the prevappNo column. If you have millions of rows in this table, thenI would suggest creating a different table, insert into it, drop the old table and then rename the new one to the original name.

     


    I feel the need - the need for speed

    CK Bhatia

  • Coolies...thanks for the responses guys; I reckon as Steve said that double cursor approach would be pretty performance hungry. I will try the method suggested by CK as I have around 200K rows.

    Cheers for the help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply