Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Case SQL Statement

Posted on 2011-09-27
6
Medium Priority
?
453 Views
Last Modified: 2012-05-12
Experts,
I am trying to "set" a variable to either 'Resolved' or 'Active'.  I tried using an update case stmt but it would only return 'Resolved' when clearly there should have been some 'Active'.

This is the stmt I am using.

declare @status varchar(20)

Update mytable
Set @Status =
Case
when field > 1 Then 'Resolved'
else 'Active'
End

The field has a number from 1 to 10 in each of the records but @status just returns 'Resolved'.

And suggestions?
Thanks,
Kelly
0
Comment
Question by:mschmidt14
[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
  • 2
  • 2
6 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36712648
Hi Kelly,

Close.  :)

You just need to SELECT the result....


 Set @Status =
SELECT
Case
when field > 1 Then 'Resolved'
else 'Active'
End
FROM mytable


Good Luck,
Kent
0
 
LVL 23

Expert Comment

by:nemws1
ID: 36712690
Is this in a stored procedure?  Your statement doesn't make much sense... since you're not UPDATE()ing anything (even then, you'd still just use SET and not UPDATE).  This would work for a SELECT.  Note: not using SET and using ":=" instead:
SELECT field,
@Status :=
CASE
WHEN (field > 1) THEN 'Resolved'
ELSE 'Active'
END AS `Status`
FROM mytable
;

Open in new window

0
 

Author Comment

by:mschmidt14
ID: 36712790
I tried both suggestions and they didn't work.  I may have been putting the code in the wrong place though.  This is the whole code so you can see what's going on.
declare @status varchar(20)

Update filteredjxe_csisurvey
Set @Status =
Case
when jxe_recommend > 1 Then 'Resolved'
else 'Active'
End

select distinct
c.jxe_recommend,
@status as status,
c.jxe_branchname,
c.jxe_deptname,
c.jxe_execidname,
c.jxe_companyidname,
c.jxe_csisurveyid,
c.jxe_caseid,
c.jxe_companyid,
Datediff(day,c.createdon, i.modifiedon) as jxe_daysopen,
CONVERT(varchar(20),c.createdon,101) as createdon,
CONVERT(varchar(20),c.modifiedon,101) as modifiedon

From filteredjxe_csisurvey c
left outer join filteredincident as i on c.jxe_caseid = i.incidentid
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36712818
Update filteredjxe_csisurvey
Set @Status =
Case
when jxe_recommend > 1 Then 'Resolved'
else 'Active'
End

That's a problem.  :(  

The Set clause of the UPDATE statement needs a column name within the table that you're updating.  @Status is a local variable.


Are you trying to update a table or set a local variable?

0
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 36712858
It almost looks like you want @Status set for each row, but its a single variable, and gets overwritten.  Even if what you had worked, @Status would just be set according to the last row in your table.

Why don't you just put the CASE statement in your SELECT?
select distinct
c.jxe_recommend,
  CASE
  WHEN c.jxe_recommend > 1 THEN 'Resolved'
  ELSE 'Active'
  END AS Status,
c.jxe_branchname,
c.jxe_deptname,
c.jxe_execidname,
c.jxe_companyidname,
c.jxe_csisurveyid,
c.jxe_caseid,
c.jxe_companyid,
Datediff(day,c.createdon, i.modifiedon) as jxe_daysopen,
CONVERT(varchar(20),c.createdon,101) as createdon,
CONVERT(varchar(20),c.modifiedon,101) as modifiedon
From filteredjxe_csisurvey c
left outer join filteredincident as i on c.jxe_caseid = i.incidentid

Open in new window

0
 

Author Closing Comment

by:mschmidt14
ID: 36713058
I didn't realize you could do that...I learn more and more with this language everyday!

Thanks so much!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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