-1 
UPDATE market_order SET is_wechat = 1 WHERE id IN ( SELECT a.id FROM market_order a LEFT JOIN market_order_detail b ON a.id = b.order_id WHERE b.template_id IN ( SELECT id FROM market_template WHERE core_id = 2 AND is_wechat = 1 ) )

Error

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

Improve this question
0

2 Answers 2

Reset to default
0

You can do it with UPDATE..JOIN instead :

UPDATE market_order a JOIN market_order_detail b ON(a.id = b.order_id) JOIN market_template t ON(b.template_id = t.id) SET a.is_wechat = 1 WHERE t.core_id = 2 AND t.is_wechat = 1

MySQL doesn't allow using a select from the target table inside the WHERE clause.

Also - when using LEFT JOIN , filters on the right table should be specified only inside the ON() clause.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it works now,many thanks!
0

You may try to make it within 1 query:

update market_order mo set is_wechat = 1 left join market_order_detail mod on mo.id = mod.order_id inner join market_template mt on mt.id = mod.teplate_id where mt.core_id = 2 and mt.is_wechat = 1
Improve this answer

1 Comment

'mod' can not be short~~ it also solved my problem,many thanks.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.