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' |
|
|
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 sjoin Enrollment e on s.personid = e.personidleft join v_ProgramParticipation pp on e.personID = pp.personIDwhere s.activeYear = 1 and s.studentNumber = '114722' and s.endDate is null |
|
|
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? |
|
|
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? |
|
|
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. |
|
|
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 earlierselect 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 |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
jpost
Starting Member
43 Posts |
Posted - 2012-09-27 : 15:04:22
|
So I tried this code and it returned 0 results:update v_ProgramParticipationset name = '235012 - LEP: Eng-only Supplement Instructional Pr'from student sjoin enrollment e on s.personID = e.personIDleft join v_ProgramParticipation p on p.personID = s.personIDwhere 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 |
|
|
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. |
|
|
|