Solved

Update Case SQL Statement

Posted on 2011-09-27
6
441 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 45

Expert Comment

by:Kdo
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Kdo
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 125 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

17 Experts available now in Live!

Get 1:1 Help Now