Post reply

update a column using the value of another column

  • September 9, 2015 at 2:48 pm

    #321017

    I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

    I would like to load this table every day from student system.

    During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

    My question in my merge statement something like below

    Merge into student st

    using (select * from InputStudent ins)

    on st.id=ins.studentid

    when matched then update

    set st.schoolid=ins.schoolid

    , st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid

    else st.previouschoolid

    end

    , st.grade_level=ins.grade_level

    ;

    My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

    Thanks

  • September 9, 2015 at 3:12 pm

    #1825548

    sqlfriends (9/9/2015)

    I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

    I would like to load this table every day from student system.

    During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

    My question in my merge statement something like below

    Merge into student st

    using (select * from InputStudent ins)

    on st.id=ins.studentid

    when matched then update

    set st.schoolid=ins.schoolid

    , st.previouschoolid= case when (st.schoolid<>ins.schoolid) then ins.schoolid

    else st.previouschoolid

    end

    , st.grade_level=ins.grade_level

    ;

    My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

    Thanks

    The schoolid in the CASE statement will be the schoolid before you ran the update statement, not the new updated value.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 9, 2015 at 3:20 pm

    #1825551

    I should change a little in my original post with the bold part.

    Could you explain a little bit?

    thanks

  • September 9, 2015 at 3:27 pm

    #1825552

    sqlfriends (9/9/2015)

    I should change a little in my original post with the bold part.

    Could you explain a little bit?

    thanks

    I was saying that, if I understand your question correctly, the answer to: "My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?" is Yes.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 9, 2015 at 3:28 pm

    #1825553

    sqlfriends (9/9/2015)

    I should change a little in my original post with the bold part.

    Could you explain a little bit?

    thanks

    If you have a = 3 and b = 4 before the update statement, what will be in a and b after the following statement is executed?

    Update someTable SET

    a = 5,

    b = a

    You would have a = 5 and b = 3

    Hope that helps.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply