Thursday, August 13, 2015

UPDATE A Column With Its Current Value With Addition (SQL)

This week I ran upon an issue in SQL where I needed to actually do a Google Search to find an answer.  I knew how to do this in Visual Basic.Net (VB.Net), but had no clue how to do it via one single SQL statement which is my preferred method.  I needed to UPDATE A Column with its current value with an additional word added.  The column I was needing to update had multiple options.  Below I will show you 2 ways to complete the same project and you see which one you prefer.

I could have designed an update for every specific possible outcome and it would have worked just fine, but here lately I try to accomplish something with one perfect query.   Below I will show you both ways that  I am talking about.  You can pick the best one for your project.

Example.

I have a table called tblTable and in that table I have a fields/columns called DocumentType and DateReceived.  I wish to ADD the year to the end of specific DocumentTypes that were received in 2015 for archiving purposes. The DocumentType I wish to archive are Certificate and Report only.
Table original called tblTable.

DocumentNameDocumentTypeDateReceived

Cow.docCertificate1/2/2015
Dog.docEntry 2/5/2015
Chicken.docReport5/5/2015
Horse.docEntry1/9/2015
Pig.doc Certificate 8/8/2014
Pony.doc Certificate 5/1/2015


Table results after the SQL statements below.

DocumentNameDocumentTypeDateReceived

Cow.docCertificate 20151/2/2015
Dog.docEntry 2/5/2015
Chicken.docReport 20155/5/2015
Horse.docEntry1/9/2015
Pig.doc Certificate 8/8/2014
Pony.doc Certificate 2015 5/1/2015


The first way would be to do 2 separate update statements that would UPDATE each specific DocumentType with the desired results.

UPDATE tblScanDocs
Set DocumentType = 'Report 2015'
where DocumentType = 'Report' and YEAR(DateReceived) = '2015'

And

UPDATE tblScanDocs
Set DocumentType = 'Certificate 2015'
where DocumentType = 'Certificate' and YEAR(DateReceived) = '2015'


The second way (the way I prefer) is to combine both of the above statements into one.


UPDATE tblScanDocs
Set DocumentType = DocumentType + ' 2015'
where DocumentType in ('Certificate' , 'Report')
and YEAR(Datereceived) = '2015'

Both ways will give you the desired outcome, but as you can see the second way would be a great time saver if you had quite a few DocumentTypes that needed to be updated.

If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...