update a column using the value of another column

Question

update a column using the value of another column

sqlfriend

SSC Guru

Points: 52781
More actions
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

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

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]

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

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

Could you explain a little bit?

thanks

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

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]

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

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/