| Author |
 Topic  |
|
|
jpost
Starting Member
43 Posts |
 Posted - 09/26/2012 : 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
Posting Yak Master
Denmark
100 Posts |
Posted - 09/26/2012 : 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 - 09/26/2012 : 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 |
|
|
|
bitsmed
Posting Yak Master
Denmark
100 Posts |
Posted - 09/26/2012 : 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 - 09/27/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/27/2012 : 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
Posting Yak Master
Denmark
100 Posts |
Posted - 09/27/2012 : 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
|
|
|
|
jpost
Starting Member
43 Posts |
Posted - 09/27/2012 : 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
India
47189 Posts |
Posted - 09/27/2012 : 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/
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
|
|
jpost
Starting Member
43 Posts |
Posted - 09/27/2012 : 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 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 09/27/2012 : 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. |
|
|
| |
Topic |
|
Upate statement inside of a case statement?
