SQL: what is wrong in my query

Question

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 ?

I do not see `tbl_student` anywhere, and you are missing a closing parenthesis. What is the target database system (SQL Server? MySql? Oracle? — D Stanley, May 31 '16 at 21:15
If you're using MySQL it's a possible duplicate of http://stackoverflow.com/questions/45494 — D Stanley, May 31 '16 at 21:17
Probably MySQL with its that stupid limitation that you cannot reference the table that you are updating. — Thorsten Kettner, May 31 '16 at 21:17

score 2 · Accepted Answer · answered May 31 '16 at 21:16

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.

EoinS · Answer 2 · 2016-05-31T21:44:53.737

0

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
  )
);

edited May 31 '16 at 21:44

answered May 31 '16 at 21:27

EoinS

5,405
1
19
32

thanks for your help your query updated only one student. the first i got works well – george May 31 '16 at 22:02

SQL: what is wrong in my query

2 Answers2