Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 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