Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I modify this query so it won't change all degree col to null.

Posted on 2011-03-15
3
Medium Priority
?
280 Views
Last Modified: 2012-05-11
I ran this query 2 or 3 times.  When I open the table, all degree column data is now null.  How can I modify this so if degree doesn't meet any of the when condition then it doesn't get changed.  thanks.
Update dbo.PersonDegree
	SET degree = 
	case 
		when (degree='M.D.') then 'MD'
		when (degree in('B.Sc.', 'B.S.', 'B.S', 'BS(High Honors)', 'BS/MS', 'B.sc.', 'BSc')) then 'BS'
		when (degree IN('Ph. D.', 'Ph D', 'Ph.D.')) then 'PhD.'
		when (degree IN('M.S.', 'Masters', 'M. Sci.')) then 'MS'
		when degree='DMD' then 'DDM'
		when degree ='Psy.D' then 'DPSY'
		when degree in('B.A', 'B.A.') then 'BA'
		when degree='B.M.S.' then 'BMS'
		when degree='D.O.' then 'DO'
		when degree='DNSC' then 'DSN'
		when degree='M.A.' then 'MA'
		when degree='M.B.B.S' then 'MD'
		when degree='MS.B' then 'MSB'
		when degree='P.A.' then 'PA'
		when degree='MSN BS' then 'MSN'
	End

Open in new window

0
Comment
Question by:lapucca
3 Comments
 
LVL 7

Accepted Solution

by:
lexlythius earned 2000 total points
ID: 35143405
See it this works

Update dbo.PersonDegree
	SET degree = 
	case 
		when (degree='M.D.') then 'MD'
		when (degree in('B.Sc.', 'B.S.', 'B.S', 'BS(High Honors)', 'BS/MS', 'B.sc.', 'BSc')) then 'BS'
		when (degree IN('Ph. D.', 'Ph D', 'Ph.D.')) then 'PhD.'
		when (degree IN('M.S.', 'Masters', 'M. Sci.')) then 'MS'
		when degree='DMD' then 'DDM'
		when degree ='Psy.D' then 'DPSY'
		when degree in('B.A', 'B.A.') then 'BA'
		when degree='B.M.S.' then 'BMS'
		when degree='D.O.' then 'DO'
		when degree='DNSC' then 'DSN'
		when degree='M.A.' then 'MA'
		when degree='M.B.B.S' then 'MD'
		when degree='MS.B' then 'MSB'
		when degree='P.A.' then 'PA'
		when degree='MSN BS' then 'MSN'
                ELSE degree
	End

Open in new window

0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35143411
add this to the end of the case statement
   

   when degree='MSN BS' then 'MSN'
   else degree=degree
End

0
 

Author Closing Comment

by:lapucca
ID: 35143423
Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question