Mysql query returns error #1093 - You can't specify target table

Question

I need to check and update with same query my database. The error says it is not possble to update same table which is included in the select statement. Is there any workaround of this to happen in 1 mysql query? Here is the query:

 $query='update option_values_to_products set available="0" where id in (
select ovtp.id from option_values ov,option_values_to_products ovtp,options o where 
ovtp.product_id="1657" and ovtp.option_values_id=ov.id and ov.options_id=o.id and
o.name="Size" group by ovtp.id )';

What you need is `update with join`. And @LuthandoLoot is right. This is duplicate. — Hotdin Gurning, Oct 09 '15 at 10:49

score 2 · Accepted Answer · answered Oct 09 '15 at 10:47

2

Yes, this is a nagging feature of mysql and there is a workaround to it: wrap the subquery within the IN() clause into another subquery.

update option_values_to_products set available="0" where id in (select id from (
select ovtp.id from option_values ov,option_values_to_products ovtp,options o where 
ovtp.product_id="1657" and ovtp.option_values_id=ov.id and ov.options_id=o.id and 
o.name="Size" group by ovtp.id ) as t)

answered Oct 09 '15 at 10:47

Shadow

33,525
10
51
64

let me test Shadow :) – thecore7 Oct 09 '15 at 10:48
It updates Shadow, thanks, I will accept your answer in 3 mins, Thank you to the rest of you guys ! – thecore7 Oct 09 '15 at 10:52

score 0 · Answer 2 · answered Oct 09 '15 at 10:56

Avoid using nested queries for many reasons like performance and memory issue, also it can be very hard to be understood for the next developers

Good practice
Split your query into 2 parts :

<?php
 $qSelect = 'select ovtp.id from option_values ov,option_values_to_products ovtp,options o where 
             ovtp.product_id="1657" and ovtp.option_values_id=ov.id and ov.options_id=o.id and 
             o.name="Size" group by ovtp.id';

  $res = DATABASE_MANAGER::exec($qSelect);

  $qUpdate = 'update option_values_to_products set available="0" where id in (' . implode(",", $res) .')';
  $res2 = DATABASE_MANAGER::exec($qUpdate);

Mysql query returns error #1093 - You can't specify target table

2 Answers2