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.

DocumentName	DocumentType	DateReceived
Cow.doc	Certificate	1/2/2015
Dog.doc	Entry	2/5/2015
Chicken.doc	Report	5/5/2015
Horse.doc	Entry	1/9/2015
Pig.doc	Certificate	8/8/2014
Pony.doc	Certificate	5/1/2015

Table results after the SQL statements below.

DocumentName	DocumentType	DateReceived
Cow.doc	Certificate 2015	1/2/2015
Dog.doc	Entry	2/5/2015
Chicken.doc	Report 2015	5/5/2015
Horse.doc	Entry	1/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...