Sql Server Ignore update errors

Question

I have inherited a very old database that needs some data to be updated. Each row ha a column with a UniqueID that looks like C0042-45-39612. The last 3 numbers of the code are the category Id (in this case 612).

I need to do an update that targets only certain categories and I'm using this SQL statement

UPDATE WebbikesProducts SET Price = Price * 1.05 WHERE Convert( Integer, SubString( UniqueID, 3, 3 )) = 125

The obvious problem here is what if the unique code doesn't have 3 numbers at the end? Well that's exactly the problem I have as not all the items are categorized or have unique numbers like C0049-307-1CHROME.

I don't have access to the DB (I'm calling this from an asp page) so I'd prefer not to have to create a stored procedure. The DB is SQL 2000.

Is there any way to ignore the rows with errors and carry on updating the other rows?

Answer 1

I'm not sure why you are bothering converting to an int. Why not just do a string compare for the last three digits. Also, you are doing - substring(id, 3, 3).

I assume you have simplified the above snippet to make it easier to read and that you are already extracting the 39612 first?

I would suggest the following:

where UniqueID like '%612'

Answer 2

Try this:

UPDATE WebbikesProducts 
SET Price = Price * 1.05 
WHERE ISNUMERIC(SubString( UniqueID, 3, 3 )) = 1
AND Convert( Integer, SubString( UniqueID, 3, 3 )) = 125

or even more simple:

UPDATE WebbikesProducts 
SET Price = Price * 1.05 
WHERE SubString( UniqueID, 3, 3 ) = '125'

-Edo