Lakhan Baheti Lakhan Baheti - 5 months ago 6
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 ?

Thanks

Answer

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;