SQL UPDATE с подзапросом, который ссылается на ту же таблицу в MySQL

Я пытаюсь обновить значение столбца в кучке строк в таблице, используя UPDATE. Проблема в том, что мне нужно использовать подзапрос, чтобы получить значение для этого столбца, и это зависит от той же таблицы. Здесь запрос:

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';

Обычно, если учитель и ученик были в двух разных таблицах, mysql не жаловался. Но поскольку они оба используют одну и ту же таблицу, mysql вместо этого выводит эту ошибку:

ОШИБКА 1093 (HY000): вы не можете указать целевую таблицу "ученик" для обновления в разделе FROM

Есть ли способ заставить mysql выполнить обновление? Я на 100% уверен, что предложение from не будет затронуто по мере обновления строк.

Если нет, есть ли другой способ я могу написать это обновление sql для достижения такого же эффекта?

Спасибо!

РЕДАКТОР: Я думаю, что у меня это получилось:

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';

Ответы

Ответ 1

Некоторая ссылка для вас 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

Ответ 2

Абстрактный пример с более четкими именами таблиц и столбцов:

UPDATE tableName t1
INNER JOIN tableName t2 ON t2.ref_column = t1.ref_column
SET t1.column_to_update = t2.column_desired_value

Как было предложено @Nico

Надеюсь, что это поможет кому-то.

Ответ 3

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'

Выше приведен пример запроса на обновление...

Вы можете написать дополнительный запрос с помощью инструкции SQL SQL, вам не нужно указывать псевдоним для этой таблицы. укажите имя псевдонима для таблицы подзапросов. Я пробовал, и он отлично работает для меня....

Ответ 4

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';

Ответ 5

Я получаю сообщение "Ошибка: недопустимое использование групповой функции", когда я это делаю.

Но это сообщение: запрос mysql для обновления поля до max (поле) + 1

показывает работу с еще более вложенным подселекцией, которая работает.

Ответ 6

Мне это нужно для SQL Server. Вот он:

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

Я думаю, что он работает с другими RDBMS (пожалуйста, подтвердите). Мне нравится синтаксис, потому что он расширяемый.

Формат, который мне нужен, был на самом деле:

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

Ответ 7

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';