Clicky

Experts.

I don't know if this is possible but I would like to update a table using a case statement but I also want to show the results, with what I thought would be a select statement but it's not working.

I have been able to update the table, but I can't get the results to show.

This is one version that I have tried without success.

update filteredjxe_csisurvey
set jxe_status =
  CASE  WHEN jxe_recommend > 1
 THEN 'Resolved'
  ELSE 'Active'
  END
Where jxe_recommend in
(Select jxe_status
From filteredjxe_csisurvey
Where jxe_recommend = 1 or jxe_recommend > 1)

Any ideas?

Thanks,
Kelly

asked 12/16/2011 02:28

mschmidt14's gravatar image

mschmidt14 ♦♦


3 Answers:
by the way...

Where jxe_recommend = 1 or jxe_recommend > 1
--> same as
Where jxe_recommend >= 1

and add a select at the end

declare @myDate=getdate();

update filteredjxe_csisurvey
set updatedon = @myDate, ... the rest of the query;

select * from  filteredjxe_csisurvey where jxe_status in ('Resolved', 'Active') where updatedon  = @myDate;
link

answered

HainKurt's gravatar image

HainKurt

No.  You need separate commands:  one to update; one to display.

update filteredjxe_csisurvey
set jxe_status =
  CASE  WHEN jxe_recommend > 1
 THEN 'Resolved'
  ELSE 'Active'
  END


Select jxe_status
From filteredjxe_csisurvey
Where jxe_recommend >= 1


But, instead maybe you should forgo the physical column and just use a view or use a computed column for jxe_status.  


create view filteredixe_csisurvey_view
as
select *,
  CASE  WHEN jxe_recommend > 1
 THEN 'Resolved'
  ELSE 'Active'
  END as  jxe_status
From filteredjxe_csisurvey
go


select * From filteredjxe_csisurvey_view


OR

alter table filteresix_csisurvey
   drop column jxe_status
go
alter table filteresix_csisurvey
   add jxe_status as
select  CASE  WHEN jxe_recommend > 1
 THEN 'Resolved'
  ELSE 'Active'
  END
go

select * From filteredjxe_csisurvey

 


link

answered 2011-12-16 at 10:43:01

dqmq's gravatar image

dqmq

I used the first statement of the 3 suggested.  

Thanks much!
Kelly
link

answered 2011-12-16 at 10:48:43

mschmidt14's gravatar image

mschmidt14

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×132

Asked: 12/16/2011 02:28

Seen: 237 times

Last updated: 12/16/2011 04:06