update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update statement

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-16 : 01:22:40

hi
i am having table like this,i am doing scd2 type..
so if any update happen i want to insert new row then flag i want to change new row "Y" old row "N"

ex
sk_id stu_id stuname class flag
1 10 kk A Y<-- old row to N
2 11 ss A Y
3 12 pp S Y
now i update stu_id 10
4 10 qq A Y<-- new row

update <table_name> set
Flag='y'
where STU_SK NOT IN (select STU_SK from <TABLE_NAME>
where STU_SK not in( select MAX(STU_SK) from <TABLE_NAME>
group by STU_ID))

i used query like this sql server

now i am creating another one table ie,stu_course_bg

stu_sk course_sk date flag
1 1000 11/12/89 Y
2 2000 12/05/96 Y
3 1500 25/12/97 Y
1 4500 03/05/96 Y

now my student table updated so i fatch only flag "y" row
so in stu_sk i want to replace 1 to 4 so here 2 row is updated
but when i used my flag update statement i will update only 1 row how to update both the 2 rows how to write sql query for that

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 01:24:34

seems like what you need is an insert trigger on stu_sk table to update the stu_course_bg for each type 2 inserts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 01:56:29

something like this would do


CREATE TRIGGER courseupdate
ON studenttable
FOR INSERT
AS
BEGIN
UPDATE c
SET c.stu_sk = i.sk_id
FROM stu_course_bg c
INNER JOIN studentable s
ON s.sk_id = c.stu_sk
AND s.flag = 'N'
INNER JOIN inserted i
ON i.stu_id = s.stu_id 
AND i.flag = 'Y'
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources