SQL Server Performance Forum – Threads Archive
Update Querry
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
You should first alter that column to varchar datatype before updating Madhivanan Failing to plan is Planning to fail
Seems like you’re trying to convert varchar to int but you said you changed the column datatype to Varchar from int.
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
Post table structure and your update statement Madhivanan Failing to plan is Planning to fail
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.
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
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.
Ok. Now I understood. Thank you so much. Madduri
]]>