[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Update Case SQL Statement

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
mschmidt14
Asked:
mschmidt14
  • 2
  • 2
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
nemws1Commented:
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
 
mschmidt14Author Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
nemws1Commented:
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
 
mschmidt14Author Commented:
I didn't realize you could do that...I learn more and more with this language everyday!

Thanks so much!!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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