SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Upate statement inside of a case statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jpost
Starting Member

43 Posts

Posted - 09/26/2012 :  14:02:13  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 09/26/2012 :  14:16:01  Show Profile  Reply with Quote
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 - 09/26/2012 :  15:52:57  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 09/26/2012 :  16:15:12  Show Profile  Reply with Quote
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 - 09/27/2012 :  08:03:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 09/27/2012 :  08:43:49  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 09/27/2012 :  09:04:31  Show Profile  Reply with Quote
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 - 09/27/2012 :  13:01:21  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/27/2012 :  13:22:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 09/27/2012 :  13:29:35  Show Profile  Reply with Quote
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 - 09/27/2012 :  15:04:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 09/27/2012 :  15:38:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000