Post reply

update

  • January 3, 2011 at 1:46 pm

    #244832

    How can i update all records in a column by prefixing with letter "ASP". For example i have employeeno column with record "4398586", now i would like to update it with ASP.4398586

  • January 3, 2011 at 2:20 pm

    #1268677

    OK, I retract my reply.

    Tara should consider the valid concerns listed.

  • January 3, 2011 at 2:46 pm

    #1268684

    STOP!! There is a hidden trap...

    What is the data type of the current column employeeno?

    If it's any numeric data type (e.g. INT), the update statement will fail.

    Before updating a column the way you're planning to do you need to make sure that it not used as a foreign key reference.

    Furthermore, you'd nee to check each and every reference of this column in procedures, functions, triggers, calculated columns, views etc. if the change you're planning to do will not cause any error while processing the data.

    I do expect such an error since I'm guessing there is a data type change. Furthermore, you won't be able to add a new employeeno by simply using MAX()+1, just to name one.

    If you immediately need such a value as you're requesting I'd rather go with a persisted computed column parallel to the original column until each and every piece of your software is validated.

    So please, DO NOT USE homebrew01's code until you fully understand the effect of it!


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 3, 2011 at 7:38 pm

    #1268724

    LutzM (1/3/2011)

    STOP!! There is a hidden trap...

    What is the data type of the current column employeeno?

    If it's any numeric data type (e.g. INT), the update statement will fail.

    Before updating a column the way you're planning to do you need to make sure that it not used as a foreign key reference.

    Furthermore, you'd nee to check each and every reference of this column in procedures, functions, triggers, calculated columns, views etc. if the change you're planning to do will not cause any error while processing the data.

    I do expect such an error since I'm guessing there is a data type change. Furthermore, you won't be able to add a new employeeno by simply using MAX()+1, just to name one.

    If you immediately need such a value as you're requesting I'd rather go with a persisted computed column parallel to the original column until each and every piece of your software is validated.

    So please, DO NOT USE homebrew01's code until you fully understand the effect of it!

    You also forgot to add that it violates every form of data normalcy. 😉

    Tara... don't do this. Like Lutz says, "it's a trap" that you will end up paying for until the database reaches end of life and goes away.

    --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)

  • January 3, 2011 at 7:57 pm

    #1268727

    If I am prefixing numbers instead of letters it will sum the value like

    update mytable empno='2004'+'.'+empno and the result is summing up 2004 + 4398586

    but i want it as 2004.4398586, how can i do this?

  • January 3, 2011 at 9:29 pm

    #1268735

    Maybe explain why you want to do it, and there might be a better solution.

  • January 4, 2011 at 6:47 am

    #1268901

    I just want to prefix all those records as per the year which i want to do every year.

  • January 4, 2011 at 7:53 am

    #1268971

    Consider adding a computed column, it could be safer than updating a column that might have dependencies.

    what I would do (and I expect to be shot down in flames by Jeff for this!) is create a scalar function that takes employee number and the year from a 'createdate' or similar field and return a varchar(16) or similar with the prefix based on the year followed by the '.' and the employee no cast as a varchar(12)

    set this field to 'persisted' and you'll only get an overhead when you write the record to DB and not every time you query it.

    you would just need to make sure that you update your function each time a new year comes around or possibly prepopulate the next 10 years assuming you know what the codes will be.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 4, 2011 at 7:56 am

    #1268976

    LutzM (1/3/2011)

    STOP!! ... I'd rather go with a persisted computed column parallel to the original column

    oh look you got there first.... 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 5, 2011 at 8:27 am

    #1269550

    When I saw the thread in the daily newsletter the first thing I thought of as well was just running the update script to prepend the ASP. portion onto the column, then I saw the word number as well and then I considered the relationships with other tables. I also considered the computer column as well.

    The only thing I really have to offer is seeing this ASP seems to be a constant so I don't even know if it really is needed in the table; rather you may just be able to use it as a prefix in whatever client script would be calling the data

    Director of Transmogrification Services

  • January 5, 2011 at 11:30 am

    #1269665

    How you approach this really depends on how the table and column are being used and what applications or reports that currently reference the table would be affected. If 'ASP' should be prefixed for all employee numbers, then consider creating a view that selects from the table and also contains an additional column with a concatenated version of employeeno. For example:

    create view v_Employees

    as

    select

    employeeno,

    'ASP' + employeeno as employeeno_ext,

    ...

    ...

    from Employees

    GO

    If employeeno is numeric datatype, then cast it as varchar prior to concatenating it.

    For example:

    'ASP' + cast( employeeno as varchar(20) ) as employeeno_ext

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 5, 2011 at 7:09 pm

    #1269839

    Tara-1044200 (1/3/2011)

    If I am prefixing numbers instead of letters it will sum the value like

    update mytable empno='2004'+'.'+empno and the result is summing up 2004 + 4398586

    but i want it as 2004.4398586, how can i do this?

    I'd just add a column for the year and, as someone already suggested, a persisted computed column with the forumula...

    newyearcolumn+'.'+CAST(empno AS VARCHAR(10))

    Heh... and Ben is correct... no scalar functions for this please. 😉

    --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)

  • January 6, 2011 at 3:02 am

    #1269954

    heh heh heh

    I like scalar functions! They're functional and scalar!

    Seriously though most of the SQL work I do is based around writing reports on data that we have copied over from an OLD informix database so the most advanced stuff I see normally is an index on a char(12) column. Seriously, who has char(12) as a surname column? really?

    I'm only just starting to find out about the stuff SQL Server can do, can you recommend any performance related reading for SQL functions?

    Have fun.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 7, 2011 at 10:13 pm

    #1271027

    BenWard (1/6/2011)

    heh heh heh

    I like scalar functions! They're functional and scalar!

    Seriously though most of the SQL work I do is based around writing reports on data that we have copied over from an OLD informix database so the most advanced stuff I see normally is an index on a char(12) column. Seriously, who has char(12) as a surname column? really?

    I'm only just starting to find out about the stuff SQL Server can do, can you recommend any performance related reading for SQL functions?

    Have fun.

    I've seen lots of posts and articles on the subject and read and remembered what they say, but I didn't save any of the links.

    I guess my recommendations would be are to avoid scalar and multi-line Table Valued Functions (mTVF's) in favor of inline Table Valued Functions (iTVF's) and to use CROSS APPLY to implement the iTVF's. All other "tuning" techniques apply including things like making SARGable code, etc, etc. Even if you need something that will return a scalar value, the recommendation is to still use an iTVF to return just one row/column and, again, use CROSS APPLY to implement it.

    Just like just about everything else in SQL, those are "general" recommendations and there will always be exceptions.

    --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 14 posts - 1 through 13 (of 13 total)

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