This article introduces how to solve the #1093 error in the MySQL SQL UPDATE statement. If you have any questions, please refer to it.
I used such an SQL statement:
The Code is as follows: |
Copy code |
UPDATE 'student _ quiz_answer 'set 'Article _ id' = (SELECT qa. article_id, sqa. answer_id FROM student_quiz_answer sqa left join 'quiz _ answer 'qa ON sqa. answer_id = qa. answer_id where qa. article_id> 0) |
The problem is that when MySQL updates a Table field, if the Update value contains the Select statement of its own Table, the following error occurs:
#1093-You can't specify target table 'student _ quiz_answer 'for update in FROM clause
However, the SQL Update value must be queried based on the current Table field.
Use the Update value as a new Table, such as t2. The table alias is t1. The Condition Clause indicates that the values of t1 and t2 are equal.
For the correct implementation method, you need to create an intermediate table. The content of the intermediate table is exactly the same as that of the frjg table, and then modify the frjg table, there is a "primary-foreign key relationship" between the intermediate table and the frjg table (this relationship does not exist), and it is changed to such an SQL statement:
The Code is as follows: |
Copy code |
UPDATE 'student _ quiz_answer 'T1, (SELECT qa. article_id, sqa. answer_id FROM student_quiz_answer sqa left join 'quiz _ answer 'qa ON sqa. answer_id = qa. answer_id where qa. article_id> 0) t2 SET t1. 'Article _ id' = t2.article _ id WHERE t1.answer _ id = t2.answer _ id |
Summary
1093 error: but when modifying a table, the subquery cannot be the same table. Solution: Set the subquery layer to the original table's grandson query. For example:
Insert into gg set id3 = (select c. a + 1 from (select max (id3) as a from gg) c) This is correct
However, if insert into gg set id3 = (select max (id3) as a from gg), error 1093 is returned.