Mise à jour SQL avec sous-requête qui renvoie à la même table dans MySQL
j'essaye de mettre à jour la valeur d'une colonne dans un tas de lignes dans une table en utilisant UPDATE. Le problème est que j'ai besoin d'utiliser une sous-requête pour obtenir la valeur de cette colonne, et il repose sur la même table. Voici la requête:
UPDATE user_account student
SET student.student_education_facility_id = (
SELECT teacher.education_facility_id
FROM user_account teacher
WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'
)
WHERE student.user_type = 'ROLE_STUDENT';
Normalement si l'enseignant et l'élève étaient dans 2 tables différentes, mysql ne se plaindrait pas. Mais puisqu'ils utilisent tous les deux la même table, mysql rejette cette erreur à la place:
erreur 1093 (HY000): vous ne pouvez pas spécifier la table cible 'student' pour la mise à jour dans de la clause
est-ce que je peux forcer mysql à faire la mise à jour? Je suis sûr à 100% que la clause "from" ne sera pas affectée lors de la mise à jour des lignes.
si ce n'est pas le cas, y a-t-il un autre moyen d'écrire cette mise à jour sql pour obtenir le même effet?
Merci!
EDIT: je pense que je l'ai eu à travailler:
UPDATE user_account student
LEFT JOIN user_account teacher ON teacher.user_account_id = student.teacher_id
SET student.student_education_facility_id = teacher.education_facility_id
WHERE student.user_type = 'ROLE_STUDENT';
7 réponses
quelque référence pour vous http://dev.mysql.com/doc/refman/5.0/en/update.html
UPDATE user_account student
INNER JOIN user_account teacher ON
teacher.user_account_id = student.teacher_id
AND teacher.user_type = 'ROLE_TEACHER'
SET student.student_education_facility_id = teacher.education_facility_id
exemple abstrait avec des noms de tableau et de colonne plus clairs:
UPDATE tableName t1
INNER JOIN tableName t2 ON t2.ref_column = t1.ref_column
SET t1.column_to_update = t2.column_desired_value
comme suggéré par @Nico
Espérons que cela aide quelqu'un.
UPDATE user_account
SET (student_education_facility_id) = (
SELECT teacher.education_facility_id
FROM user_account teacher
WHERE teacher.user_account_id = teacher_id
AND teacher.user_type = 'ROLE_TEACHER'
)
WHERE user_type = 'ROLE_STUDENT'
ci-dessus sont la requête de mise à jour d'échantillon...
vous pouvez écrire la requête sub avec la déclaration de SQL de mise à jour, vous n'avez pas besoin de donner le nom d'alias pour cette table. donner le nom d'alias à la table de requête secondaire. J'ai essayé et ça fonctionne très bien pour moi....
UPDATE user_account student
SET (student.student_education_facility_id) = (
SELECT teacher.education_facility_id
FROM user_account teacher
WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'
)
WHERE student.user_type = 'ROLE_STUDENT';
j'en avais besoin pour SQL Server. Le voici:
UPDATE user_account
SET student_education_facility_id = cnt.education_facility_id
from (
SELECT user_account_id,education_facility_id
FROM user_account
WHERE user_type = 'ROLE_TEACHER'
) as cnt
WHERE user_account.user_type = 'ROLE_STUDENT' and cnt.user_account_id = user_account.teacher_id
je pense que cela fonctionne avec d'autres RDBMSes (Veuillez confirmer). J'aime la syntaxe parce qu'elle est extensible.
le format dont j'avais besoin était en fait:
UPDATE table1
SET f1 = cnt.computed_column
from (
SELECT id,computed_column --can be any complex subquery
FROM table1
) as cnt
WHERE cnt.id = table1.id
, j'obtiens "Erreur: Invalid utilisation de la fonction de groupe" où je le fais.
mais ce post: requête mysql pour mettre à jour le champ à max (Champ) + 1
montre une chose encore plus imbriquée, qui fonctionne.
UPDATE user_account student, (
SELECT teacher.education_facility_id as teacherid
FROM user_account teacher
WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'
) teach SET student.student_education_facility_id= teach.teacherid WHERE student.user_type = 'ROLE_STUDENT';