UPDATE table with select the same tablein multi-table queries

Question

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

score 0 · Accepted Answer · answered Sep 06 '16 at 06:49

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.

answered Sep 06 '16 at 06:49

sagi

40,026
6
59
84

it works now,many thanks! – tony he Sep 06 '16 at 07:06

score 0 · Answer 2 · answered Sep 06 '16 at 06:50

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

answered Sep 06 '16 at 06:50

Riad

3,822
5
28
39

'mod' can not be short~~ it also solved my problem,many thanks. – tony he Sep 06 '16 at 07:04

UPDATE table with select the same tablein multi-table queries

2 Answers2