SQL Server Performance

Update Querry

Discussion in 'Getting Started' started by madduri, May 10, 2007.

  1. madduri New Member

    Hi,

    I am trying to update a table. I actually created the column as int and now changed to varchar and trying to update the int values to characters.

    when I ran the 1st update it was fine but for my next update it is throwing the following error:


    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'AcqSpecialist' to data type int.

    Your help is highly appreciated.

    Thanks in advance

    Madduri
  2. Madhivanan Moderator

    You should first alter that column to varchar datatype before updating

    Madhivanan

    Failing to plan is Planning to fail
  3. BulentGucuk New Member


    Seems like you're trying to convert varchar to int but you said you changed the column datatype to Varchar from int.
  4. madduri New Member

    I did alter the column to varchar before update.

    The problem is solved when I passed the int value as a string, like '3'.

    Thank you.

    Madduri
  5. Madhivanan Moderator

    Post table structure and your update statement

    Madhivanan

    Failing to plan is Planning to fail
  6. Adriaan New Member

    So you first had an INT column with data in it. You then changed the data type to varchar. At that point, the numeric values have been replaced with the corresponding string characters.

    From what you're describing, it sounds like you originally had a substitute foreign key from a lookup table. You then changed the datatype of the foreign key column. You then replaced the substitute key values with the corresponding natural key from the lookup table. This natural key is a real alphanumeric string, so it cannot be reverted to an INT data type.

    You first need to update the natural key back to the substitute key (cast to varchar) and then revert the column to the INT data type.

    ***

    Don't mess with tables: they are not objects that you can freely manipulate, they are the basis for your application.
  7. madduri New Member

    Not exactly. I just created a table yesterday and inserted data. I changed the ID column to name column today and changed the column to varchar. Then I tried to update the int values to character values. It took the 1st update but errored out for the second.

    my 1st querry was:

    UPDATE USERROLES
    SET Rolename = 'AcqSpecialist'
    where Rolename = 1

    That was fine and the corresponding rows were updated.

    My 2nd querry was:

    UPDATE USERROLES
    SET Rolename = 'Attorney'
    where Rolename = 2

    Then it gave me the following error:

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'AcqSpecialist' to data type int.

    When I ran the querry as below the problem was solved.

    UPDATE USERROLES
    SET Rolename = 'Attorney'
    where Rolename = '2' (passed as a string)

    Thank you

    Madduri







  8. Adriaan New Member

    You were depending on SQL Server's ability to do an implicit conversion of the '1' string value present in the column, to the numeric 1 in your WHERE statement.

    As long as the column only contained 'numeric' strings on the column, this works. But as you updated the column on a number of rows to 'AcqSpecialist', the implicit conversion fails.
  9. madduri New Member

    Ok. Now I understood. Thank you so much.

    Madduri

Share This Page