I have a database that contains 3 tables (student, class and student_class). In the class table there are some classes that have their delete time set to NOT NULL. I want to update the delete time of the student of these classes to the current time.
The short table schema is as follows:
student(id, name, ..., delete_time)
class(id, name, ..., delete_time)
student_class(id, studentId, classId)
the query i tried :
UPDATE student SET delete_time = now() WHERE id IN (
SELECT student.id FROM student, student_class,class WHERE
student.id = student_class.studentId AND
student_class.classId= class.id AND
class.delete_time IS NOT NULL
#1093 - Table 'tbl_student' is specified twice, both as a target for 'UPDATE' and as a separate source for data, is there anything with query ?
In MySQL, you would do this using
update student s join student_class sc on s.id = sc.studentid join class c on c.id = sc.classid set s.delete_time = now() where c.delete_time is not null;
FROMclause. Always use explicit