MySQL SQL UPDATE statement #1093 error Solution

Source: Internet
Author: User
Tags id3

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.