在MySQL中,当您尝试在同一个查询中同时从表中选择数据并更新该表时,可能会遇到错误 ERROR 1093 (HY000): You can't specify target table 'sc' for update in FROM clause
。这是因为MySQL默认不允许在 FROM
子句中引用正在被更新的表。
为了解决这个问题,您可以使用不同的方法来更新记录:
方法1:使用不同的连接(JOIN)
使用join语句创建一个临时被使用的表避免sc表被直接使用,防止出现ERROR 1093 (HY000): You can't specify target table 'sc' for update in FROM clause
这样的错误。
您可以使用 JOIN
来避免直接在 FROM
子句中引用正在被更新的表。
UPDATE student_grades AS sg1 JOIN ( SELECT AVG(grade) AS avg_grade FROM student_grades AS sg2 WHERE sg2.course_id = 2 ) AS avg_course_grade SET sg1.grade = avg_course_grade.avg_grade WHERE sg1.student_id = '200215121' AND sg1.course_id = 2;
UPDATE student_grades AS sg1 JOIN ( SELECT AVG(grade) AS avg_grade FROM student_grades AS sg2 WHERE sg2.course_id = 2 ) AS avg_course_grade SET sg1.grade = avg_course_grade.avg_grade WHERE sg1.student_id = '200215121' AND sg1.course_id = 2;
方法2:使用子查询在 SET
子句中
在MySQL 8.0及以上版本中,您可以在 SET
子句中使用子查询,即使该子查询引用了正在被更新的表。
UPDATE student_grades SET grade = ( SELECT AVG(grade) FROM student_grades AS sg2 WHERE sg2.course_id = 2 ) WHERE student_id = '200215121' AND course_id = 2;START TRANSACTION;
请注意,这种方法在MySQL 8.0之前的版本中可能不可用。
方法3:使用事务
如果您需要确保数据的一致性,可以使用事务来确保更新操作的原子性。
START TRANSACTION; UPDATE student_grades SET grade = ( SELECT AVG(grade) FROM student_grades AS sg2 WHERE sg2.course_id = 2 ) WHERE student_id = '200215121' AND course_id = 2; COMMIT;
使用事务可以确保在更新过程中,如果出现任何问题,您可以回滚到更新之前的状态。
方法4:使用临时表
如果上述方法都不适用,您可以考虑使用临时表来存储平均成绩,然后使用这个临时表来更新目标记录。
CREATE TEMPORARY TABLE IF NOT EXISTS temp_avg_grades ( avg_grade DECIMAL(10, 2) ); INSERT INTO temp_avg_grades SELECT AVG(grade) FROM student_grades WHERE course_id = 2; UPDATE student_grades SET grade = ( SELECT avg_grade FROM temp_avg_grades ) WHERE student_id = '200215121' AND course_id = 2; DROP TEMPORARY TABLE IF EXISTS temp_avg_grades;