September 23, 2008 at 3:04 pm
In the Northwind database. Using the employees table as an example. There are currently 9 rows in that table. Lets assume that in the "title" column there are only 2 possible values A. Sales Representative and B. Sales Manager. Ideally I would like to find the first occurnace of "Sales Manager" and change the value of the "TitleofCourtesy" to "man". So in the table employeeid's 1,3,and4 all have a Title as "Sales Representative". The next row (employeeid 5) has a "title" of "Sales Manager" therefore I would like to change his "TitleofCourtesy" to "man". BUT, anyone below him with a title of Sales manager should not be affected. Im only looking to change the first row with a different value then quit. Im trying to use the test table as an example because my live enviroment is much more complex, but the idea(s) are identical. Id appreciate anyhelp you guys can offer as ive been racking my brain and cannot figure out how to update only the first row found that matches my criteria. Thanks!!!!
September 23, 2008 at 3:12 pm
What's first? You need an order to determine that.
You can select top 1 ... order by xxx as a subselect and then use that as the update.
for multiple rows it gets complex, but you really want a grouping set.
September 23, 2008 at 3:57 pm
To Clarify:
I know i should provide DDL code to create the table/data but unforunetly I dont know how to do that. BUT, I have the below code:
select case when a1.program <> a2.program then 'update' else 'dont update' end as 'test'
,a2.academic_term
,a2.program
,a2.academic_year
from people as p
inner join academic as a1
on p.people_code_id=a1.people_code_id
and a1.academic_term='fall'
and a1.academic_year='2008'
inner join academic as a2
on p.people_code_id=a2.people_code_id
where p.people_code_id='p000009626'
order by a2.academic_year desc
,a2.academic_term
Which correctly pulls the correct person (based on the people code Id). This is the correct select script, BUT, how can I modify this and turn it into an update script where it updates the returned record OR the record with "update" in the case field? Any help would be MUCH MUCH appreciated as im on a very tight time constraint and am stressing out.
**Edit**
IM now lost. The above code pulls ALL records for the specified person. I just want to update the first record that has a different value in the "program" field and leave all other records alone. ERRR.
September 23, 2008 at 4:28 pm
My result set on the above code looks like this:
dont updateFALLPT2008
updateSPRINGFT2008
updateFALLFT2007
updateSPRINGFT2007
updateFALLFT2006
updateSPRINGFT2006
I just want to update the FIRST SPRING FT 2008 RECORD. Since it has a value of FT instead of PT like his Fall 2008 record.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply