tabla - update con subconsulta mysql

ACTUALIZACIÓN SQL con sub-consulta que hace referencia a la misma tabla en MySQL

Question

Estoy intentando actualizar el valor de una columna en un grupo de filas en una tabla usando ACTUALIZAR. El problema es que necesito usar una subconsulta para derivar el valor para esta columna, y depende de la misma tabla. Aquí está la consulta:

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

Por lo general, si el maestro y el alumno se encontraban en 2 tablas diferentes, mysql no se quejaría. Pero dado que ambos usan la misma tabla, mysql vomita este error en su lugar:

ERROR 1093 (HY000): no se puede especificar el ''alumno'' de la tabla de objetivos para la actualización en la cláusula FROM

¿Hay alguna manera de forzar a mysql a hacer la actualización? Estoy 100% seguro de que la cláusula from no se verá afectada a medida que se actualicen las filas.

Si no, ¿hay alguna otra forma en que pueda escribir esta actualización sql para lograr el mismo efecto?

¡Gracias!

EDITAR: Creo que lo hice funcionar:

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

Answer 1

Alguna referencia para usted 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

Answer 2

Ejemplo abstracto con nombres de tabla y columna más claros:

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

Según lo sugerido por @Nico

Espero que esto ayude a alguien.

Answer 3

Lo necesitaba para SQL Server. Aquí está:

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

Creo que funciona con otros RDBMS (confirma). Me gusta la sintaxis porque es extensible.

El formato que necesitaba era esto en realidad:

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

Answer 4

Obtengo "Error: uso no válido de la función de grupo" cuando lo hago.

Pero esta publicación: consulta mysql para actualizar el campo a max (campo) + 1

muestra una cosa de subselección aún más anidada, que funciona.

Answer 5

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

Arriba está la consulta de actualización de muestra ...

Puede escribir una sub consulta con una declaración SQL de actualización, no necesita dar un nombre de alias para esa tabla. dar nombre de alias a la tabla de sub consulta. Lo intenté y está funcionando bien para mí ...

Answer 6

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

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