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

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