Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Upate statement inside of a case statement?

Author  Topic 

jpost
Starting Member

43 Posts

Posted - 2012-09-26 : 14:02:13
I want to update a table using a update command inside of a case statement. Can this be done, here is a sample of my code:

Case
when e.englishProficiency = (M)then (UPDATE v_ProgramParticipation pp
set pp.name = '235012'
where personid = '6781')
end as 'code'

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 14:16:01
Correct syntax, given the information you provided, would be:

update v_ProgramParticipation
set [name]='235012'
where personid='6781'
and englishProficiency='M'

Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 2012-09-26 : 15:52:57
There are two different tables being used, sorry forgot to mention that, here is my entire script:
select distinct s.studentnumber, s. lastname, s.firstName, e.englishProficiency, e.personID,
Case
when e.englishProficiency = (M)then (UPDATE v_ProgramParticipation pp
set pp.name = '235012 - LEP: Eng-only Supplement Instructional Pr'
where personid = '6781')
end as 'code'
from Student s
join Enrollment e on s.personid = e.personid
left join v_ProgramParticipation pp on e.personID = pp.personID
where s.activeYear = 1 and s.studentNumber = '114722' and s.endDate is null
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 16:15:12
I have never seen an update statement inside a select statement.
I'm not quite sure exactly that you aim to do.

My best guess would do it in two steps: first run an update, and then select the values you want.

Can you explain further? Especially what you expect the field "code" to hold?
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 2012-09-27 : 08:03:44
When the english proficiency field is set to M or Y, I want to then place the value of 235012....... into the name field. Right now that field is blank. In our system, this selection is a drop down value that is seen on the screen. Does any of this help?
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-27 : 08:43:49
It is not possible.
Also I am not able to understand the sense.

Maybe you can try to explain your problem instead of posting code that isn't working please?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-27 : 09:04:31
This sql will give you the english proficiency field, replaced as to the specification you gave, in the replaces_name field.
This will however NOT update the table.
If you need to update the table, use the update sql I gave you earlier

select distinct s.studentnumber
,s.lastname
,s.firstName
,e.englishProficiency
,e.personID
,case when pp.personid='6781' and e.englishProficiency in ('M','Y')
then '235012 - LEP: Eng-only Supplement Instructional Pr'
else pp.name
end as replaced_name
from Student as s
inner join Enrollment as e
on e.personid=s.personid
left outer join v_ProgramParticipation as pp
on pp.personID=e.personID
where s.activeYear=1
and s.studentNumber='114722'
and s.endDate is null
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 2012-09-27 : 13:01:21
Ran the script and it didn't change the name field, it still came back as a null value. Any other suggestions would be great
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 13:22:28
unless you provide some sample data and explain we will not able to get your full scenario and suggest you a proper solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-27 : 13:29:35
Also, a left join doesn't make much sense if you want to update that table. Maybe you need to take a step back and provide DDL, DML and expected output. That will go a long way to help us help you. Here are some links that can help guide you on how to supply that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 2012-09-27 : 15:04:22
So I tried this code and it returned 0 results:
update v_ProgramParticipation
set name = '235012 - LEP: Eng-only Supplement Instructional Pr'
from student s
join enrollment e on s.personID = e.personID
left join v_ProgramParticipation p on p.personID = s.personID
where s.personID = '6781'

I think the problem is that the personID that I am trying to add the value to does not exist in the v_programparticipation table. So how would I add this value or record first?
Thanks
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-27 : 15:38:13
See the links from my post on 09/27/2012 : 13:29:35 for how to post a question with all the required information.
Go to Top of Page
   

- Advertisement -