Scott Milburn (2/27/2013)
I have four tables that have a varchar data type which gets imports or daily user updates and this field is an account number field which always has a length of 10, including leading zeros. My current code uses:UPDATE table.Statements
SET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')
The issue is that this runs against all four tables and takes around 5 minutes. After the code runs, I see that it touched every record, which I don't want. I just want the code to look at the rows that this field needs to be changed on and disregard the previously trimmed rows.
I know I need some kind of IF statement to accomplish this, but not sure how.
TIA, Scott
If the majority of rows do not need to be updated, then you could just do this:
UPDATE table.Statements
SET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')
WHERE field03 <> REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')
It would still have to look at every row, but the actual update would be avoided and that is the most expensive part of the process.