• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

update query with case and like

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
johnnyg123
Asked:
johnnyg123
  • 4
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
johnnyg123Author Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
johnnyg123Author Commented:
Perfect!!!

Thanks Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now