Mysql--You can ' t specify target table ' address ' for update on FROM clause

Source: Internet
Author: User

When you do address management, you need to set the other address of the user to be non-default based on the user you want to set as the default address.

Need to select the user ID and update

Original statement

Update address Set Isdeafult = 0 where user_id = (select user_id from address where id =?)

Error--can ' t specify target table ' address ' for update in FROM clause

The main idea is that you cannot select some values from the same table before you update the table (in the same statement)

The modified statements are as follows

UPDATE address a INNER JOIN (SELECT user_id from address WHERE id = #{id}) c SET a.isdeafult = 0 WHERE a.user_id = c.user_ Id

Analytical

SELECT * FROM address a INNER JOIN
(SELECT user_id from address where id = 1) c WHERE a.user_id = c.user_id

The content is exactly equal to the select * from address, which essentially uses the inner join to make an intermediate table query

Note: This error only exists in MySQL

Mysql--You can ' t specify target table ' address ' for update on FROM clause

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.