Server 5.7 I am writing this way I get an error

DELETE FROM agent_view_closure WHERE Agc_Path IN SELECT CONCAT(savc.Agc_Path, CONCAT('-', 8)) AS path FROM agent_view_closure savc WHERE savc.Agc_Oag_Id_Desc = 4

11:40:56 Kernel error: Error (1064) 42000: syntax; syntax; syntax; syntax;

Now I write separately

 DELETE FROM agent_view_closure avc WHERE avc.Agc_Path IN ('4-8')

11:43:05 Kernel error: Error (1064) 42000: "Syntax; Syntax; Syntax; Syntax; Error 4-8 ')' at line 1 "

That's how it works

 DELETE FROM agent_view_closure WHERE Agc_Path IN ('4-8')

But writes Warning

 11:44:07 Warning: 1681. 'EXTENDED' is deprecated and will be removed in a future release.

The fact is that it needs to be written on symfony

 $into = $qb->select([ $qb->expr()->concat('savc.path', $qb->expr()->concat("'-'", ':descendant')) ]) ->from(AgentViewClosure::class, 'savc') ->where('savc.descendant = :agent') ->getDQL(); $qb->resetDQLParts(); $qb->delete() ->from(AgentViewClosure::class, 'avc') ->where($qb->expr()->in('avc.path', $into)) ->setParameters([ 'descendant' => $descendant->getId(), 'agent' => $agent->getId() ]); $test = $qb->getQuery()->getResult();

I get the error I go with the first

SQLSTATE [HY000]: General error: 1093 You can't specify target table

Does anyone have any idea how to solve this?

  • The warning answer seems to be here at stackoverflow.com/questions/33585144/… But for the rest of xs, it seems impossible to write an alias to Delete - Serge Esmanovich
  • What do you need then? why IN in the query, if the syntax is IN(var1,var2,var3) but you have only one value. - Naumov
  • @Naumov Imagine that there are 2 of them, then Select is written there which can return not one value but several, for example 2 or 3 or even half a hundred - Serge Esmanovich
  • But in general, in the first request, the parentheses are missing what you get now is IN '4-8' instead of IN ('4-8') should be something like IN(select id from user) for example. - Naumov
  • @Naumov tried with quotes on parentheses all options, the problem is that the deletion follows the same table as select, this is solved with the help of join the same situation can be with update - Serge Esmanovich

1 answer 1

In mysql you cannot delete and simultaneously select from the same table. Costs through nested from-subquery or multi-form delete. For details, see MySQL error 1093 and 1235