MS SQL Server
  1. MS SQL Server
  3. Vérifier l'unicité dans l'instruction de mise à jour
Intereting Posts
L'user n'a pas créer de message d'erreur d'permissions de table Est-ce un bogue SQL SERVER? SQL Server: Comment puis-je copyr des données d'une table vers une autre table sans que les caractères 'e +' ne soient ajoutés aux résultats utiliser des jeux de sauvegarde lors de l'exécution de sauvegardes du journal des transactions Quelle est la manière la plus simple d'utiliser T-SQL / MS-SQL pour append une string à des cellules de tableau existantes? Calcul basé sur la ligne SQL ci-dessus Comment puis-je save un object C # complexe dans la database à l'aide d'ADO.NET et de procédures stockées? Puis-je utiliser un paramètre DSN pour récupérer plusieurs lignes? Conception de database pour les parameters user Obtenir la valeur de return de la procédure stockée et l'utiliser dans le programme Sauvegarde / restauration à partir d'une database différente provoquant l'échec de l'access exclusif à la restauration impossible Comment configurer / activer l'authentification basée sur le formulaire dans SSRS 2008 RS SQL Server 2008 – Mise à jour uniquement d'un nombre spécifique de lignes dans une table suggérer une méthode pour mettre à jour datatables dans plusieurs tables avec des données randoms? SQL: Ajout d'une key étrangère sur une key composite existante

Vérifier l'unicité dans l'instruction de mise à jour

J'ai une table qui relie une class aux étudiants qui sont dans cette class: 

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

Si je veux transférer toutes les classs d'un étudiant à un autre (retirer un étudiant de toutes les classs auxquelles il est inscrit et append un autre étudiant à chacune des classs auxquelles l'ancien élève a été inscrit), j'utilise la requête suivante: 

 update class_student set student_id = @newStudent where student_id = @oldStudent and class_id not in (select class_id from class_student where student_id = @newStudent) delete from class_student where student_id = @oldStudent

Comment puis-je transférer les cours de plus d'un étudiant au nouvel étudiant? Je ne peux pas mettre where student_id in (@oldStudent1, @oldStudent2) parce que si les deux anciens étudiants sont dans la même class, après avoir exécuté la requête ci-dessus il y aura une violation de la contrainte unique. Aussi, je voudrais faire la mise à jour en si peu de requêtes si possible (je pourrais simplement exécuter les requêtes ci-dessus deux fois, mais j'aimerais le faire en less).

J'utilise SQL Server 2008 R2.

Edit: Pour clarifier, voici un exemple: 

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

Cela signifie que l'élève 1 est en class 1 et 3, l'élève 2 est en class 1 et 4, et l'élève 3 est en class 2, 3 et 4. Si je veux transférer tous les cours de l'élève 1 à l'élève 3, exécuterait la requête suivante: 

 update class_student set student_id = 3 where student_id = 1 and class_id not in (select class_id from class_student where student_id = 3) delete from class_student where student_id = 1

Nos données ressembleraient à ceci: 

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

Si, au lieu de cela, j'avais exécuté cette requête: 

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

En ignorant la contrainte unique sur la table, datatables ressembleraient à ceci: 

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

L'logging double (1, 3) est ce que j'essaie d'éviter, car il entraînera une violation de contrainte unique dans la table.

Lors de la configuration de la table d'origine, vous devez toujours inclure un identifiant de ligne unique avec lequel referencer une ligne spécifique (voir ci-dessous la colonne 'identity' appelée 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)

Dans une situation où les élèves 1 et 2 partent et que vous passez les cours qu'ils suivaient à l'élève 3 (à less que l'élève 3 ne participe déjà à ces cours), le code pourrait ressembler à ceci: 

 WITH CTE AS ( SELECT row_Id,class_id,student_id,RN = ROW_NUMBER()OVER(PARTITION BY class_id ORDER BY class_id) FROM class_student WHERE student_id in (1,2,3) ) DELETE FROM class_student where class_id in (select class_id from class_student group by class_id having count(class_id) > 1) and student_id <> 3 and row_id not in (select row_id from cte where student_id <> 3 and rn >= 2) Update class_student set student_id = 3

J'utilise une 'expression de table commune' avec 'RANK' pour numéroter chaque class_id en fonction du nombre de lignes portant le même class_id. Pour voir cela, vous pouvez exécuter le code ci-dessous après avoir créé la table class_student et inséré datatables (voir en haut) mais avant d'exécuter le code CTE ci-dessus: 

 WITH CTE AS ( SELECT row_Id,class_id,student_id,RN = ROW_NUMBER()OVER(PARTITION BY class_id ORDER BY class_id) FROM class_student WHERE student_id in (1,2,3) ) SELECT * FROM CTE

Comme les ID de class 1,3 et 4 sont dupliqués, ils ont une valeur de 2 dans la colonne RN (numéro de ligne).

J'utilise ce résultat dans le CTE pour supprimer les lignes dont nous n'avons pas besoin de la table class_student et c'est là que l'on voit l'importance de toujours avoir un row_id unique.

La requête Supprimer supprime les lignes de la table class_student qui sont des duplicates ID de class. Dans le cas d'une class fréquentée à la fois par l'élève 3 et par l'un ou les deux autres élèves, il prend les rangées où l'élève n'est pas 3 (parce que l'élève 3 ne part pas).

Pour le faire avec succès (sans prendre les lignes que nous voulons conserver pour être assignées à l'élève 3), il faut (en comparant row_id) que les rangées où RN = 2 (ie class_id est dupliqué) et student_id n'est pas 3 sont conservées pour que nous conserver une des rangées pour les classs que les élèves 1 et 2 faisaient, mais pas l'élève 3 (c.-à-d. où ni student_id n'avait 3).

Enfin, mettez à jour toutes les lignes restantes dans la table à un student_id de 3 afin que Student 3 obtienne tous les cours.

Pour voir le résultat, vous pouvez exécuter: 

 select * from class_student

Je pense que vous aurez besoin d'au less 2 instructions DML pour atteindre votre objective. Et si vous en avez vraiment besoin en une fois, vous pouvez envelopper les instructions dans une procédure stockée. 

 insert into class_student (class_id, student_id) select distinct class_id, @newStudent from class_student where student_id in (@oldStudent1, @oldStudent2) and class_id not in (select class_id from class_student where student_id = @newStudent); delete from class_student where student_id in (@oldStudent1, @oldStudent2);

EDIT: Correction d'insertion pour inclure la clause "not in".