george george - 6 months ago 9
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 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


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

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

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;

Notes:

  • 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.
Comments