Lakhan Baheti Lakhan Baheti - 1 year ago 70
SQL Question

Update query having inner query referencing same table

update goods_inward_outward
set is_active = 0
where gi_go_id IN (select gi_go_id from goods_inward_outward where gi_go_id <100)

I m getting following error

1093 - You can't specify target table 'goods_inward_outward' for update in FROM clause

Whats is the problem in above ?
Is this because i am using update and select query on same table?
If so, what can be alternative solving ?


Answer Source

One way of doing this in MySQL is to use a join. However, in your case, the join key and the where clause are using the same column. So, no additional table reference is necessary:

update goods_inward_outward gio
    set is_active = 0
    where gi_go_id < 100;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download