sql - Check for uniqueness within update statement -

i have table links class students in class:

create table class_student     (class_id int,      student_id int,      constraint class_student_u unique nonclustered (class_id, student_id)) 

if want transfer classes 1 student (remove 1 student classes he/she enrolled in , add student each of classes old student enrolled in), use following query:

update class_student set student_id = @newstudent student_id = @oldstudent , class_id not in (select class_id                      class_student                      student_id = @newstudent)  delete class_student student_id = @oldstudent 

how can transfer classes more 1 student new student? can't put where student_id in (@oldstudent1, @oldstudent2) because if both old students in same class, after running above query there violation of unique constraint. also, i'd update in few queries if possible (i run above queries twice, i'd in fewer).

i'm using sql server 2008 r2.

edit: clarify, here's example:

class_id student_id =================== 1        1 1        2 2        3 3        1 3        3 4        2 4        3 

this means student 1 in class 1 , 3, student 2 in class 1 , 4, , student 3 in class 2, 3, , 4. if want transfer classes student 1 student 3, run following query:

update class_student set student_id = 3 student_id = 1 , class_id not in (select class_id                      class_student                      student_id = 3)  delete class_student student_id = 1 

our data this:

class_id student_id =================== 1        3 1        2 2        3 3        3 4        2 4        3 

if, instead, had run query:

update class_student set student_id = 3 student_id in (1, 2) , class_id not in (select class_id                      class_student                      student_id = 3)  delete class_student student_id in (1, 2) 

ignoring unique constraint on table, data this:

class_id student_id =================== 1        3 1        3 2        3 3        3 4        3 

the double (1, 3) record i'm trying avoid, because cause unique constraint violation in table.

when setting original table should include unique row id reference specific row (please see below 'identity' column called row_id):

drop table class_student create table class_student ( row_id int identity(1,1), class_id int, student_id int, constraint class_student_u unique nonclustered (class_id, student_id) ) insert class_student (class_id,student_id) values (1,1) insert class_student (class_id,student_id) values (1,2) insert class_student (class_id,student_id) values (2,3) insert class_student (class_id,student_id) values (3,1) insert class_student (class_id,student_id) values (3,3) insert class_student (class_id,student_id) values (4,2) insert class_student (class_id,student_id) values (4,3) 

in situation students 1 , 2 leaving , passing classes taking student 3 (unless student 3 attending classes), code this:

with cte ( select row_id,class_id,student_id,rn = row_number()over(partition class_id order class_id) class_student student_id in (1,2,3) ) delete class_student class_id in (select class_id class_student  group class_id having count(class_id) > 1) , student_id <> 3 , row_id not in (select row_id cte student_id <> 3 , rn >= 2) update class_student set student_id = 3 

i using 'common table expression' 'rank' number each class_id according number of rows bearing same class_id. see can run code below after creating class_student table , inserting data (see top) before run cte code above:

with cte ( select row_id,class_id,student_id,rn = row_number()over(partition class_id order class_id) class_student student_id in (1,2,3) ) select * cte 

because class_id 1,3 , 4 duplicated, have value of 2 in rn (row number) column.

i'm using result in cte delete rows don't need class_student table , importance of having unique row_id can seen.

the delete query deletes rows in class_student table class id duplicates. in case of class attended both student 3 , 1 or both of other students takes rows student id not 3 (because student 3 not leaving).

to (without taking rows want retain assigned student 3), requires (by comparing row_id's) rows rn = 2 (i.e. class_id duplicated) , student_id not 3 retained keep 1 of rows classes both student 1 , 2 doing student 3 not (i.e. neither student_id 3).

finally, update remaining rows in table student_id of 3 student 3 gets courses.

to see result can run:

select * class_student