1

I may be doing something wrong, I searched for it before and I find some workarounds that tell me that it can't be possible on MySQL, others are posting that it's due to MySQL optimizer so you can simply turn it off and continue, but it's not working to me.

I only want a simple workaround to deal with this. The only table where I can find id_address_delivery and id_address_invoice is from the orders table, this data is on other tables too but can be null so there's no option to search for it on another place.

SET optimizer_switch = 'derived_merge=off';

update orders 
set id_customer = (select id_customer from customer where email like 'foo@foo.com'),
id_address_delivery = (select id_address_delivery from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1),
id_address_invoice = (select id_address_invoice from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1)
where id_customer = (select id_customer from customer where email like 'foo2@foo2.com');

i'm getting Error Code: 1093. You can't specify target table 'orders' for update in FROM clause on mysql workbench, even if I appply 

SET optimizer_switch = 'derived_merge=off';

Are there any option to deal with this situation? i thank on setting some vars, for example:

SET @iad = (select id_address_delivery from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1);

then setting this var as value like:

id_address_delivery = @iad;

I'm not getting an error response when doing it but it lasts really a lot (i exactly don't know why) and the tiemout message appear (30 seconds) I tried putting it at 120 seconds and getting the same timeout message.

EDIT:

I tried with alias but with no result. Same error:

update orders AS sor
set sor.id_customer = (select id_customer from customer where email like 'foo@foo.com'),
sor.id_address_delivery = (select a.id_address_delivery from orders as a where a.id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1),
sor.id_address_invoice = (select b.id_address_invoice from orders as b where b.id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1)
where pso.id_customer = (select id_customer from customer where email like 'foo2@foo2.com');

/EDIT:

I readed some posts that are tagged as the same issue but there I find some workarounds for other clauses and I can't figure out how to apply the same with my particular case.

How may I proceed? Thanks.

Sumesh TG
  • 2,557
  • 2
  • 15
  • 29
Joel Bonet Rodríguez
  • 123
  • 1
  • 14
  • 1
    Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – Nick Sep 10 '18 at 10:17
  • i read this post before ask, it does not resolve my case or at least i can't figure out how to apply this workaround to my particular case. – Joel Bonet Rodríguez Sep 10 '18 at 10:38
  • As per the linked answer, enclose the query in a subquery i.e. `(select * from (select id_address_delivery from orders ...))` – Nick Sep 10 '18 at 10:43

3 Answers3

0

As other answer, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

Check below questions same your issue and they are solved.

MySQL Error 1093 - Can't specify target table for update in FROM clause

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

  • 1
    Can you propose a different way to solve the problem? Please do not hide such knowledge behind links only, better add that here and use the links only as a reference – Nico Haase Sep 10 '18 at 11:58
0

I finally dealt with it setting vars, still don't know why this caused a timeout before. I added null protection on where clause to not insert null values if some var init fails.

There's a copy of my working example for your interest:

use dbname;

SET SQL_SAFE_UPDATES = 0;
SET optimizer_switch = 'derived_merge=off';

#-- data asociated with wrong@foomail.com will be associated to correct@foomail.com

SET @delad = (SELECT id_address_delivery FROM orders WHERE customer = (SELECT id_customer FROM customer WHERE email LIKE 'correct@foomail.com') LIMIT 1);
SET @delin = (SELECT id_address_invoice FROM orders WHERE id_customer = (SELECT id_customer FROM customer WHERE email LIKE 'correct@foomail.com') LIMIT 1);
SET @uid = (SELECT id_customer FROM customer WHERE email LIKE 'correct@foomail.com' LIMIT 1);
SET @buid = (SELECT id_customer FROM customer WHERE email LIKE 'wrong@foomail.com' LIMIT 1);

UPDATE orders
SET id_customer = @uid,
id_address_delivery = @delad,
id_address_invoice = @delin
WHERE @uid is not null AND @delad is not null AND @delin is not null AND id_customer = @buid;

UPDATE cart SET
 id_customer = @uid,
 id_address_delivery = @delad,
 id_address_invoice = @delin
 WHERE @buid is not null AND @uid is not null AND @delad is not null AND @delin is not null AND id_customer = @buid;

It's only an abstraction of the subquery value into a variable and placed it at the point would subquery be. I did it before without needing this workaround, but i think it was on sql server (maybe).

Hope it helps someone.

Joel Bonet Rodríguez
  • 123
  • 1
  • 14
0
create view foo_data as ( select o.id_customer,o.id_address_delivery,o.id_address_invoice from  orders as o inner join customer as c on o.id_customer = c.id_customer where c.email like 'foo@foo.com' limit 1)    


update orders as o inner join foo_data as f on f.id_customer = o.id_customer
set optimizer_switch = 'derived_merge=off',
set o.id_customer = f.id_customer,
set o.id_address_delivery  = f.id_address_delivery ,
set o.id_address_invoice = f.id_address_invoice,
where o.email email like 'foo2@foo2.com';

drop view foo_data;

Please try using creating temp view
Sameer Rangrez
  • 162
  • 5