October 1, 2008 at 10:14 am
I am trying to update the field "status" in our ACADEMIC table. So far my code correctly pulls the records I need, I just need to find a way to updated based the term/year returned by the coalesce statement in the where clause. My first join "A" is being used because I need to compare the "program" field in the Fall08 term to their previous academic terms to find the first term that has a different value in the "program" field. So for example a student may be enrolled in Fa08 (my first join using alias "a") Lets say that students most recent previous academic term/year was Summer 08, my code will correctly display that record, BUT, when I try to incoperate that into a update script, using the table alias "a" it ALWAYS updates the record for Fall08, INSTEAD of, in this case, Summer 08. So basically I either need a different way of writing this, or somehow incorperate something like update coalesce(a1,a2,a3,ect) but that obviously doesnt work. So far my code is below and is correctly pulling the right academic year/term to update the "status" field. I just need help with the actual update portion. I have been working on this for over a week and am COMPLETLY stumped.
update a
set status = 'N'
from people as p
inner join academic as a --Using this to pull all students enrolled in the Fall 2008 term
on p.people_code_id=a.people_code_id
and a.academic_term='fall'
and a.academic_year='2008'
and a.credits <> 0 -- We only want to pull current ACTIVE students
and a.academic_session='main'
left outer join academic as a2
on p.people_code_id=a2.people_code_id
and a2.academic_term='Summer'
and a2.academic_year='2008'
and a2.program <> a.program --Looking for programs in previous terms that are different than the program in Fall08
and a2.academic_session='main'
left outer join academic as a3
on p.people_code_id=a3.people_code_id
and a3.academic_term='Spring'
and a3.academic_year='2008'
and a3.program <> a.program
and a3.academic_session='main'
left outer join academic as a4
on p.people_code_id=a4.people_code_id
and a4.academic_term='fall'
and a4.academic_year='2007'
and a4.program <> a.program
and a4.academic_session='main'
left outer join academic as a5
on p.people_code_id=a5.people_code_id
and a5.academic_term='summer'
and a5.academic_year='2007'
and a5.program <> a.program
and a5.academic_session='main'
left outer join academic as a6
on p.people_code_id=a6.people_code_id
and a6.academic_term='spring'
and a6.academic_year='2006'
and a6.program <> a.program
and a6.academic_session='main'
left outer join academic as a7
on p.people_code_id=a7.people_code_id
and a7.academic_term='FALL'
and a7.academic_year='2006'
and a7.program <> a.program
and a7.academic_session='main'
where coalesce(a2.academic_term, a3.academic_term, a4.academic_term, a5.academic_term,a6.academic_term,a7.academic_term,null)is not null
and coalesce(a2.academic_year,a3.academic_year, a4.academic_year, a5.academic_year,a6.academic_year,a7.academic_year,null)is not null
October 2, 2008 at 2:55 am
What you are attempting is far from clear. Maybe something like this:
DECLARE @YearStart char(4)
    ,@TermStart varchar(6)
    ,@YearEnd char(4)
    ,@TermEnd varchar(6)
SELECT @YearStart = '2006'
    ,@TermStart = 'Fall'
    ,@YearEnd = '2008'
    ,@TermEnd = 'Fall'
UPDATE academic
SET status = 'N'
WHERE EXISTS
(
    SELECT *
    FROM academic A
    WHERE A.people_code_id = academic.people_code_id
        AND A.program <> academic.program
        AND A.academic_year = @YearEnd
        AND A.academic_term = @TermEnd
        AND A.credits <> 0
        AND A.academic_session = 'main'
)
    AND academic_year +
            CASE academic_term
                WHEN 'Spring' THEN '1'
                WHEN 'Summer' THEN '2'
                WHEN 'Fall' THEN '3'
            END
    BETWEEN    @YearStart +
            CASE @TermStart
                WHEN 'Spring' THEN '1'
                WHEN 'Summer' THEN '2'
                WHEN 'Fall' THEN '3'
            END
    AND    CASE @TermEnd
            WHEN 'Spring'
            THEN CAST(CAST(@YearEnd AS int) - 1 AS char(4)) + '3'
            WHEN 'Summer'
            THEN @YearEnd + '1'
            WHEN 'Fall'
            THEN @YearEnd + '2'
        END
October 2, 2008 at 8:58 am
^^ Ill have to chew on your code a bit as im still learning myself. But this is my final and working code.
update aa
set status = 'N'
from people as p
inner join academic as a --Using this to pull all students enrolled in the Fall 2008 term
on p.people_code_id=a.people_code_id
and a.academic_term='fall'
and a.academic_year='2008'
and a.credits <> 0 -- We only want to pull current ACTIVE students
and a.academic_session='main'
inner join academic as aa--Need this table in order to pull all of a students academic records
on p.people_code_id=aa.people_code_id
left outer join academic as a2
on p.people_code_id=a2.people_code_id
and a2.academic_term='Summer'
and a2.academic_year='2008'
and a2.program <> a.program --Looking for programs in previous terms that are different than the program in Fall08
and a2.academic_session='main'
left outer join academic as a3
on p.people_code_id=a3.people_code_id
and a3.academic_term='Spring'
and a3.academic_year='2008'
and a3.program <> a.program
and a3.academic_session='main'
left outer join academic as a4
on p.people_code_id=a4.people_code_id
and a4.academic_term='fall'
and a4.academic_year='2007'
and a4.program <> a.program
and a4.academic_session='main'
left outer join academic as a5
on p.people_code_id=a5.people_code_id
and a5.academic_term='summer'
and a5.academic_year='2007'
and a5.program <> a.program
and a5.academic_session='main'
left outer join academic as a6
on p.people_code_id=a6.people_code_id
and a6.academic_term='spring'
and a6.academic_year='2006'
and a6.program <> a.program
and a6.academic_session='main'
left outer join academic as a7
on p.people_code_id=a7.people_code_id
and a7.academic_term='FALL'
and a7.academic_year='2006'
and a7.program <> a.program
and a7.academic_session='main'
where coalesce
(a2.academic_term, a3.academic_term, a4.academic_term,a5.academic_term,a6.academic_term,a7.academic_term,null)= aa.academic_term
and coalesce
(a2.academic_year,a3.academic_year, a4.academic_year, a5.academic_year,a6.academic_year,a7.academic_year,null)= aa.academic_year
If there is an easier way to write this im all open. But basically im searching student records who are enrolled in our Fall 08 term. Each year/term has a "status" which indicates can be active or inactive . So what my code is doing is searching all students enrolled in fa08 and comparing their program (either full-time or part-time) to that of their previous terms. The first previous term it finds with a different program than their current term (fall 08) it will set the status for that term to "N". Is their an easier way to write the above code? I only need to go back to Fall06 so thats why I hardcoded the year/terms using a coalesce. Does it make a little more sense now?
October 2, 2008 at 9:28 am
I do not have time to look at your code, but based on your latest explaination you could try something like:
DECLARE @TermYear char(4)
    ,@Term varchar(6)
SELECT @TermYear = '2008'
    ,@Term = 'Fall'
UPDATE academic
SET status = 'N'
WHERE EXISTS
(
    SELECT *
    FROM
    (
        SELECT A1.people_code_id, A1.academic_year, A1.academic_term
            ,ROW_NUMBER() OVER
                (
                    PARTITION BY A1.people_code_id
                    ORDER BY A1.academic_year +
                        CASE A1.academic_term
                            WHEN 'Spring' THEN '1'
                            WHEN 'Summer' THEN '2'
                            WHEN 'Fall' THEN '3'
                        END
                    DESC
                ) AS RowID
        FROM academic A1
        WHERE EXISTS
        (
            SELECT *
            FROM academic A2
            WHERE A2.people_code_id = A1.people_code_id
                AND A2.program <> A1.program
                AND A2.academic_year = @TermYear
                AND A2.academic_term = @Term
                AND A2.credits <> 0
                AND A2.academic_session = 'main'
        )
            AND A1.academic_year +
                CASE A1.academic_term
                    WHEN 'Spring' THEN '1'
                    WHEN 'Summer' THEN '2'
                    WHEN 'Fall' THEN '3'
                END
                <
                 @TermYear +
                    CASE @Term
                        WHEN 'Spring' THEN '1'
                        WHEN 'Summer' THEN '2'
                        WHEN 'Fall' THEN '3'
                    END
    ) A
    WHERE A.RowID = 1
        AND A.people_code_id = academic.people_code_id
        AND A.academic_year = academic.academic_year
        AND A.academic_term = academic.academic_term
)
October 3, 2008 at 4:33 pm
Hey Craig:
By definition, in your joins, "A" only produces a set of rows for Fall 2008, just as "A2" can only return Summer 2008. As I understand your mission, it is to update ANY row in academics that meets the criteria. As long as you are updating "A", you are only ever going to be updating Fall 2008 rows.
Change the first line to read "Update Academic" instead of "Update A"
It is perfectly legal to use:
update academic
from person p
join academic a
left join academic a2
etc., etc.
Since you say your query is getting the right term/year to update, use those in your where clause instead of trying your trick with COALESCE, which beats an isnull, but which has no magical powers.
It's 5:30 on a Friday afternoon, so forgive me for taking not taking time to code it all out, but nothing you try is going to work as long as you start with "Update A".
Let me know how it works for you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 3, 2008 at 4:35 pm
bhovious (10/3/2008)
Hey Craig:By definition, in your joins, "A" only produces a set of rows for Fall 2008, just as "A2" can only return Summer 2008. As I understand your mission, it is to update ANY row in academics that meets the criteria. As long as you are updating "A", you are only ever going to be updating Fall 2008 rows.
Change the first line to read "Update Academic" instead of "Update A"
It is perfectly legal to use:
update academic
from person p
join academic a
left join academic a2
etc., etc.
Since you say your query is getting the right term/year to update, use those in your where clause instead of trying your trick with COALESCE, which beats an isnull, but which has no magical powers.
It's 5:30 on a Friday afternoon, so forgive me for taking not taking time to code it all out, but nothing you try is going to work as long as you start with "Update A".
Let me know how it works for you.
Thanks! I overcame that issue in my above post/code by joining academic as aa without any terms specifications. I wasnt aware that I could use "update academic" even though "academic" is not a table alias. But thats good knowledge to have. I appreciate the response.
October 3, 2008 at 4:47 pm
My bad. I didn't catch that the middle post was also from you. I see how using "AA" without a term qualification got you where you needed to be.
I always update the table by its true name, and use the from and joins just as a means to get the data for the where clause. It keeps thought processes much simpler.
Good problem!
I will now go have a cold beer to celebrate your success.
Have a great weekend.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 3, 2008 at 4:51 pm
bhovious (10/3/2008)
My bad. I didn't catch that the middle post was also from you. I see how using "AA" without a term qualification got you where you needed to be.I always update the table by its true name, and use the from and joins just as a means to get the data for the where clause. It keeps thought processes much simpler.
Good problem!
I will now go have a cold beer to celebrate your success.
Have a great weekend.
Bob
Thanks! Hey I have a silly question. I am a relatively new DBA, although I have my MCDBA, and learned what I know from working in the field. Looking at the above code using coalesce and the update like I did, is that a good way of coding, or is their a cleaner way of coding it? Perhaps using variables? I couldnt figure anything out as a students previous terms can be basically anything, so without hard coding what I am looking for, im not sure there is a better way of writing it? I guess I just see so much more "advanced" coding in this sections, and am wondering how my novice coding compares.
October 3, 2008 at 7:47 pm
To me, this looks like a great place to use a common table expression.
Create the CTE with a union of all your left joined queries and then do a simple join on your update with the CTE. I think it would be easier to read and thus easier to maintain in the future.
;WITH CTE AS
(
SELECT people_code_id,academic_term,academic_year,credits,academic_session, Program
FROM academic a
JOIN people p
ON p.people_code_id=a.people_code_id
AND a.academic_term='Summer'
AND a.academic_year='2008'
AND a.academic_session='main'
UNION
SELECT people_code_id,academic_term,academic_year,credits,academic_session, Program
FROM academic a
JOIN people p
ON p.people_code_id=a.people_code_id
AND a.academic_term='Spring'
AND a.academic_year='2008'
AND a.academic_session='main'
... -- Add the rest
)
UPDATE aa
SET status = 'N'
FROM people AS p
INNER JOIN academic AS aa--Need this table in order to pull all of a students academic records
ON p.people_code_id=aa.people_code_id
INNER JOIN academic AS a --Using this to pull all students enrolled in the Fall 2008 term
ON p.people_code_id=a.people_code_id
AND a.academic_term='fall'
AND a.academic_year='2008'
AND a.credits <> 0 -- We only want to pull current ACTIVE students
AND a.academic_session='main'
INNER JOIN CTE
ON p.people_code_id = CTE.people_code_id
AND aa.academic_term = CTE.academic_term
AND CTE.academic_year = aa.academic_year
... etc... Since I don't actually have data or table structure I can't fully do this and test it...
Personally, I prefer to update the table alias rather than the base table. This way I know what I'm actually updating 🙂
Gary Johnson
Sr Database Engineer
October 4, 2008 at 5:07 pm
Although it has worked very well in the present and in the past, I wonder if folks understand that Updating an alias is actually an undocumented feature? 😉
--Jeff Moden
October 6, 2008 at 3:26 pm
I never knew it was an undocumented feature. I've been doing it for years. It just seemed intuitively obvious.
As we say down here "Even a blind squirrel finds some nuts."
October 6, 2008 at 3:37 pm
Dangit... every time I see a Moden-comment I get befuzzled. Do you read every Forum thread? Let me clarify that I don't ever update an alias, always the base table name. But I didn't know updating an alias was undocumented either.
Back to you, Craig. Even without a CTE, you don't need to set up variables or anything. You are over-thinking your problem. All you are trying to do is determine a set of rows from academic to be updated with "N". Try building a select query, using aliases if you want, that will render that rowset for you. Then replace the select line with
Update Academic
Set status = 'N'
Don't worry, it isn't going to update any tables other than the one specified in your Update statement. The joins, and any where clauses, are simply limiting the set of rows to be updated.
Don't think that advanced means complicated. There is genius in seeing the simplicity in what appears to be complex. E=MC2 and all that. Keep working and thinking and you'll get there. Probably before I will. 😉
October 6, 2008 at 3:42 pm
Jeff Moden (10/4/2008)
Although it has worked very well in the present and in the past, I wonder if folks understand that Updating an alias is actually an undocumented feature? 😉
Nope, didn't know that. But I'm still going to continue to use it 🙂
Gary Johnson
Sr Database Engineer
October 6, 2008 at 4:03 pm
bobhovious (10/6/2008)
Dangit... every time I see a Moden-comment I get befuzzled.
Is that good or bad? 🙂
--Jeff Moden
October 6, 2008 at 4:29 pm
Somewhat embarassing to get caught in typos that increase volumes :w00t:, or quick reads that miss the point being made. But I'll survive.
You never answered the question: Do you look at ALL the threads?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply