Post reply

One time update

  • December 2, 2008 at 5:38 am

    #194528

    Hi all,

    I want to perform one time update for giving incremented number to a field.

    I have a table called project, which has 663 records, now i have created a new field 'proj_pjid - (int)'. i have written a trigger which auto generates the proj_pjid when project is created ex: new project number will be '664', it generates the id with respect to the count on projects, all old project values are null.

    Now i want to design a update query by which all old project numbers are changed from null to numbers ie;1,2,.......663. i dont have any idea to design this, kindly help.

    Note: There is one id- proj_projectid which is the default field, proj_pjid is a custom field which i have created.

    TIA.

  • December 2, 2008 at 6:16 am

    #906170

    You can use a ROW_NUMBER function.

    UPDATE T

    SET [Field] = Row_num

    FROM

    YourTable T

    INNER JOIN

    (SELECT [PrimaryKey], ROW_NUMBER() OVER (ORDER BY PrimaryKey) Row_num FROM YourTable) T2

    ON T.[PrimaryKey]= T2.[PrimaryKey]

    where [PrimaryKey] the primary key on the table and [Field] - the field that has to be updated

  • December 2, 2008 at 6:30 am

    #906177

    sal527 (12/2/2008)

    i have written a trigger which auto generates the proj_pjid when project is created ex: new project number will be '664', it generates the id with respect to the count on projects, all old project values are null.

    What's wrong with an identity column?

    Would you mind posting the trigger for review? There are several ways of doing such an auto-inc, most either produce wrong results occationally or cause severe locking problems.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 2, 2008 at 8:10 am

    #906250

    I agree about the use of an identity column instead of a trigger, but if you are determined to stick with your existing setup, the following will assign consecutive project IDs to your nulls.

    declare @counter int

    set @counter = 0

    update projectTbl

    set @counter = projid = @counter+1 -- "double equal" is key to incrementing columns on update

    where projID is null

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • January 19, 2009 at 5:23 am

    #929127

    Thanks to all, the issue has resolved and i am sorry as i could not reply at the same time.

  • January 19, 2009 at 7:40 pm

    #929457

    sal527 (1/19/2009)

    Thanks to all, the issue has resolved and i am sorry as i could not reply at the same time.

    Two way street here, chum... would you mind telling us how "the issue has resolved"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 12, 2009 at 6:33 am

    #942787

    As i am working on other important task, i cannot post it right away, i will try to post that before 20th of this month.

  • February 12, 2009 at 7:51 am

    #942873

    The rest of us have other "important tasks" too, but some people took time out from their day to help you.

    I think that your failure to take a few minutes to cut and paste is quite rude; and to paraphrase Dr. Hannibal Lectre:

    "Whenever possible, I prefer to eat the rude."

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • February 14, 2009 at 3:02 pm

    #944162

    My Appoligises to all, who felt i am rude, i am still a learner and this forum helped me a lot to learn many new things and build up my carrier, i got the chance to work with a very good company where i am trying to fix up things with big efforts.

    I will try to post it very soon, atleast after fininshing my present task,hope you all understand this.

    I once again thank all the members of the forums who helped me allways and i hope they are allways there for me to share there knowledge and teach me.

    Thank you.

  • February 14, 2009 at 6:01 pm

    #944172

    It's obvious that you took the path to the dark side or you would have simply said that you used the Identity column. World of hurt awaits you with the trigger if you ever decide to add more than one project at a time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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