updating junction table - 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

updating junction table

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2011-01-02 : 10:55:18

Hi all:

I have a table studentsubject with fivecolumns (id, studentid and subjectid, createteddate,modifieddate). it is a jusnction table between student table and subject table. Now I have a scenerio where user can add/remove subjects so, I want to add the newly added subjectid in studentsubject junction table if subject for that student doesn't exist and make creaetddate = getdate. I want to update modifieddate = getdate() if subject for that student already exists. subjectids are passed as CSV( 1,2,3).

Please suggest me solution to this.

Regards,
Asif Hameed

sparrow37
Posting Yak Master

148 Posts

Posted - 2011-01-02 : 10:58:59

I am using this query but it is going in insert
I am using this query( split is UDF to return table from csv)

if exists (select * from jtblStudentSubject where studentid = 6 and subjectid in (select data from dbo.split('5,6,3',',')) )
begin
update [jtblStudentSubject] set modifieddate = GETDATE()
where studentid = 6
print 'update'
end
else
begin
insert into jtblStudentSubject(StudentID,SubjectID,,createdDate)
values (6,(select data from dbo.Split('5,6,3',',')),1,GETDATE())
print 'here'
end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-02 : 17:31:55

That will update all subjects if any of the subjects exist for the student and only insert if none of them exist.
try

update [jtblStudentSubject] set modifieddate = GETDATE()
where studentid = 6
and subjectid in (select data from dbo.split('5,6,3',','))

insert into jtblStudentSubject(StudentID,SubjectID,,createdDate)
select 6,s,data,1,GETDATE())
from (select data from dbo.Split('5,6,3',',')) s
left join jtblStudentSubject t
on t.SubjectID = s.data
and t.StudentID = 6
where t.SubjectID is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources