Update and Delete

alter table StudentMajor add DepartmentId int null
go

update StudentMajor
	set DepartmentId = d.DepartmentId
	from StudentMajor sm
	join Major m
	on sm.MajorId=m.MajorId
	join MajorProgram mp
	on m.ProgramID=mp.ProgramID
	join Department d
	on mp.DepartmentID=d.DepartmentID
	join Term t
	on sm.TermId=t.TermId

select * from StudentMajor

alter table StudentMajor drop column DepartmentId
go

begin transaction

delete StudentMajor
	from StudentMajor sm
	join Major m
	on sm.MajorId=m.MajorId
	join MajorProgram mp
	on m.ProgramID=mp.ProgramID
	join Department d
	on mp.DepartmentID=d.DepartmentID
	where d.DepartmentId=1

rollback transaction