george george - 2 years ago 78
MySQL Question

SQL: what is wrong in my query

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 FROM student, student_class,class WHERE = student_class.studentId AND
student_class.classId= AND
class.delete_time IS NOT NULL

but it did not work i got an error says :

#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 ?

Answer Source

In MySQL, you would do this using update with join:

update student s join
       student_class sc
       on = sc.studentid join
       class c
       on = sc.classid
    set s.delete_time = now()
    where c.delete_time is not null;


  • The error you get is specific to MySQL; you cannot modify a table and include it in a subquery.
  • Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.
  • Table aliases make a query easier to write and to read.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download