Solved

T-SQL CASE UPDATE Statement

Posted on 2013-05-30
4
552 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
[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
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39207661
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
ID: 39207722
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
ID: 39207778
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
ID: 39207828
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

710 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