Error in mysql: Solutions for 1093 You can't specify target table for update in FROM clause

discover problems

The following sql statement was recently written while processing data from one or more databases:

UPDATE f_student
SET school_id = 0
WHERE
 id > (
 SELECT
 id
 FROM
 f_student
 WHERE
 school_id = M
 LIMIT 1
 )
AND id < (
 (
 SELECT
 id
 FROM
 f_student
 WHERE
 school_id = M
 LIMIT 1
 ) + N
)

The sql above is trying to modify the data for an interval, but running in test environment 1 reports the following error:

[Err] 1093  �  You can't specify target table  ' f_student' for update in FROM clause

The implication is obvious: there is no update operation on tables that are queried, that is, there is a subquery in our where condition, and the subquery is also for tables that require an update operation, which mysql does not support.

Resolvent

Check the Internet once, for this problem can be achieved by "bypass". See the sql statement below.

UPDATE f_student SET school_id = 0 WHERE
                     id > 
                     (
                     SELECT id FROM ( 
                         SELECT id FROM f_student WHERE school_id = M LIMIT 1 
                       ) AS temp 
                     )
                     AND id <
                    (
                     (
                      SELECT id FROM ( 
                              SELECT id FROM f_student WHERE school_id = M LIMIT 1 
                             ) AS temp_1 
                     ) + N
                    )

ok, no problem at all.The above sql, compared with the previous sql, only rounds around when fetching id and obtains id through a subquery instead of directly.

summary

This is the whole content of this article. I hope that the content of this article can help you to learn or work. If you have questions, you can leave a message to exchange.