CODEWITHSUNDEEP
mysqlsql
george
george

Reputation: 97

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 ?

Upvotes: 1

Views: 140

Answers (2)

EoinS
EoinS

Reputation: 5482

Because you could have multiple links between different students and classes in your student_class you can't create a cross-table by selecting FROM multiple tables. You could create a nested SELECT 

UPDATE student s SET s.delete_time = now() WHERE s.id IN (
  SELECT sc.student_id from student_class sc where sc.student_id = s.id AND sc.class_id IN (
    SELECT c.Id FROM Class c WHERE sc.class_id = c.id AND delete_time IS NULL
  )
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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.

Upvotes: 2

Related Questions