[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update query with case and like

Posted on 2012-09-05
6
Medium Priority
?
352 Views
Last Modified: 2012-09-05
I have a table named DepartmentUsers

Here is some sample data

UserID     LastName    FirstName     Title                            Department
1234         Doe             John               Accounting Clerk
2345         Smith          Jane               Accounting Manager
5678         Jones           Jone              Auditor - Accounting
7890         Street          Della            Manager- Finance
8989         Mason          Perry           Director Marketing  


Trying to write an update query that will look for accounting  or Finance anywhere in the title
and set Department to Finance

If Marketing any where in title need marketing to be the department value

Using example data above,  looking for following results after query

UserID     LastName    FirstName     Title                            Department
1234         Doe             John               Accounting Clerk        Finance
2345         Smith          Jane               Accounting Manager   Finance
5678         Jones           Jone              Auditor - Accounting    Finance
7890         Street          Della            Manager- Finance         Finance
8989         Mason          Perry           Director Marketing      Marketing




tried using combination of case statement and like in update statement but can't seem to get it to work

Any ideas?
0
Comment
Question by:johnnyg123
  • 4
  • 2
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38368368
Two update queries using LIKE

UPDATE DepartmentUsers
SET Department = 'Finance'
WHERE UCASE(Title) LIKE '%ACCOUNTING%' OR UCASE(Title) LIKE '%FINANCE%'

UPDATE DepartmentUsers
SET Department = 'Marketing'
WHERE UCASE(Title) LIKE '%MARKETING%'
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38368418
>If Marketing any where in title need marketing to be the department value
So ... what do you want Department to be if the title does not contain Accounting, Finance, or Marketing?
0
 

Author Comment

by:johnnyg123
ID: 38368530
Thanks Jim

Here's a little more info....the department field is new

Need to populate it based on title values

I have a couple different department tests I need to account for (just didn't want to list them all in sample data)

I was hoping to stream line them in a single case statement

If a situation was not covered in one of the case conditions....the department value would just be left blank
0
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.

 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38368712
The below works in T-SQL as one statement.  
Run it to make sure it works, then get rid of the DROP/CREATE, change #tmp to DepartementUsers, and modify to suit your needs.

Also, my first T-SQL had an error, UCASE should have been UPPER.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO

CREATE TABLE #tmp (UserID int, LastName varchar(20), FirstName varchar(20), Title varchar(50), Department varchar(20))
GO

INSERT INTO #tmp (UserID, LastName, FirstName, Title)
VALUES 
	(1234, 'Doe', 'John', 'Accounting Clerk'), 
	(2345, 'Smith', 'Jane', 'Accounting Manager'),
	(5678, 'Jones', 'Jone', 'Auditor - Accounting'),
	(7890, 'Street', 'Della', 'Manager- Finance'),
	(8989, 'Mason', 'Perry', 'Director Marketing')

UPDATE #tmp
SET Department = CASE
	WHEN UPPER(Title) LIKE '%ACCOUNTING%' OR UPPER(Title) LIKE '%FINANCE%' THEN 'Finance'
	WHEN UPPER(Title) LIKE '%MARKETING%' THEN 'Marketing' END

SELECT * FROM #tmp

Open in new window

0
 

Author Closing Comment

by:johnnyg123
ID: 38368795
Perfect!!!

Thanks Jim
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38368819
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

834 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