Executing an Update query on a production database is like driving full speed in opposite direction in a one-way traffic! You never know when your vehicle meets with an accident. A little carelessness in putting up the where clause can create havoc in your database. And an irreparable damage if you forgot to include the where clause!
But there are times when you can’t avoid the update on production server. Due to poor coding and testing practices, during initial deployments of your application to production server you may encounter formatting issues in some of the records. See for example, in the following figure: the records that are marked inside the three red rectangles.
Takeaways from this Article
- Learn escape sequence. In this example, we’ll escape single quote in T-SQL by using two single quotes (not double quotes).
- Implementation of While Loop.
- Implementation of SQL Server table variable.
Note: Example shown here is using Microsoft SQL Server – 2012 edition but should work equally well with SQL Server 2005 and 2008 / R2. To some extent this idea can also be used to other RDBMS as well.
Yes, those extra single quote at the beginning and end of the phone number. You might have deployed a fairly stable version of your application to production but unfortunately it may happen. Though these records are correct their mal-formatted structure will create issues in search operations. If you’re not rectifying them, you might need to perform some extra operations which might affect the performance of your application.
In this example, you see less than 10 records that needs to be updated. You can do this manually without executing any query. But what if you’ve say 100+ records hidden in a table with million records and that too not in any sequence? Then you must use some advanced queries to update the query.
How to fix it?
Observe if there’s a pattern in the mal-formatted records. If yes, your job of updating the records become simple. In the above example, what’s the pattern? It’s the single quote. To convert the mal-formatted records to well-formed one, you just need to replace the single quotes. What could be the possible steps to rectify this issue:
- Identify these records.
- If the records are less in numbers you can update it one by one.
- If the records are high in number then you need to opt for some row wise operation.
- For row wise operation, I don’t suggest cursors as it may affect performance, it’s better to use a table variable with while loop. But choice is yours.
Now let us create our sample table using the following code:
The above code will create the desired table. Insert a few sample records as shown in the first image. Now have a look at the following figure. Yes, you got it… the figure not only identifies the mal-formatted records but has also rectified them.
Now check the following query. The above output is produced by line# 14 of below code. Line# 14 has single quotes only, don’t confuse it with double quote. Apart from going through the code, carefully read the comments also to fully understand the code.
After executing the above query you’ll get the well-formed records. How was it? Please share your views in comment section.
Basant Singh
Latest posts by Basant Singh (see all)
- Go Maps Introduction for Beginners - May 20, 2015
- Go Programming – Arrays and Slices - May 11, 2015
- Golang Switch Case with Sample Code - May 1, 2015
