Solved

T-SQL CASE UPDATE Statement

Posted on 2013-05-30
4
539 Views
Last Modified: 2013-05-30
Hello:

Below is a script that I'm trying to run that is saying "If the pay code is a salary pay code for employee ELLI0001, then set the field UPR00100.Primary_Pay_Record = 1 else 0".

But, SQL tells me that there are syntax errors.  Can someone please tell me what I'm doing wrong?  It would be much appreciated!

SELECT CASE WHEN UPR00400.PAYRCORD = 'SALY' AND UPR00400.EMPLOYID = 'ELLI0001'
THEN UPDATE UPR00100 SET Primary_Pay_Record = '1'
Else WHEN UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001'  
THEN UPDATE UPR00100.Primary_Pay_Record = '0' End
INNER JOIN UPR00100 ON UPR00400.EMPLOYID = UPR00100.EMPLOYID

Thanks!

TBSupport
0
Comment
Question by:TBSupport
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
You can't do a SELECT ... CASE UPDATE, you can do an UPDATE ... WHERE {criteria}.

<air code, something like this>

UPDATE UPR00100
SET Primary_Pay_Record = '1'
WHERE Pay_code = 'whatever salary pay code is' AND EMPLOYID = 'ELLI0001'

UPDATE UPR00100
SET Primary_Pay_Record = '0'
WHERE Pay_code <> 'whatever salary pay code is' AND EMPLOYID = 'ELLI0001'

btw if Primary_Pay_Record is numeric then lose the single quote marks
0
 
LVL 7

Expert Comment

by:Ross Turner
Comment Utility
Hi  TBSupport,

I created this example of case statement on a update

http://sqlfiddle.com/#!3/9454c/4

Script:
Select * from UPR00100;

UPDATE UPR00100
SET Primary_Pay_Record  = 
CASE 
  WHEN Pay_code =  1 AND EMPLOYID = 'ELLI0001' THEN 1
  WHEN Pay_code =  2 AND EMPLOYID = 'ELLI0001' THEN 0
  ELSE Primary_Pay_Record
END;

Select * from UPR00100;

Open in new window


Mock Table:
 create table UPR00100(EMPLOYID varchar(30),Primary_Pay_Record int ,Pay_code int)


INSERT INTO UPR00100(EMPLOYID,Primary_Pay_Record,Pay_code) VALUES('ELLI0001',NULL,'1');
INSERT INTO UPR00100(EMPLOYID,Primary_Pay_Record,Pay_code) VALUES('ELLI0001',NULL,'2');
INSERT INTO UPR00100(EMPLOYID,Primary_Pay_Record,Pay_code) VALUES('BOBY001',NULL,'1');
INSERT INTO UPR00100(EMPLOYID,Primary_Pay_Record,Pay_code) VALUES('BOBY002',NULL,'2');
INSERT INTO UPR00100(EMPLOYID,Primary_Pay_Record,Pay_code) VALUES('BOBY003',NULL,'3');

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
UPDATE	U1
SET	Primary_Pay_Record = CASE WHEN U4.PAYRCORD = 'SALY' THEN 1 ELSE 0 END
FROM	UPR00100 U1 INNER JOIN
	UPR00400 U4 ON U1.EMPLOYID = U4.EMPLOYID
WHERE	U4.EMPLOYID = 'ELLI0001'

Open in new window

0
 
LVL 7

Accepted Solution

by:
Ross Turner earned 500 total points
Comment Utility
i realised i got your requirements wrong try this

WORKING EXAMPLE
http://sqlfiddle.com/#!3/c59b7/1

UPDATE UPR00100
SET Primary_Pay_Record  = 
CASE 
  WHEN UPR00400.PAYRCORD = 'SALY'  AND UPR00400.EMPLOYID = 'ELLI0001'  THEN 1
  WHEN UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN 0
END
FROM UPR00100  INNER JOIN
	 UPR00400  ON UPR00100.EMPLOYID = UPR00400.EMPLOYID

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now