Creating a MySQL Query in one step beside of two

Question

I'm trying to create an event on my database which will be executed every 6 hours, Currently I'm doing this daily and it's annoying because sometimes I forget to do it, I'm doing this on 2 steps

Select A.id_ from
timemanagement_Assignment as A
Left join project_Task as T on T.id_ =A.task_id_
Left join project_Phase as P on P.id_ =T. phase_id_
Left join project_Iteration as I on I.id_ =P. iteration_id_
Left join project_Lot as L on L.id_ =I. Lot_id_
Left join project_Project as Pr on Pr.id_ =L. project_id_
Where A.isfavoris_ =true and Pr.status_ in("Closed", "Archived","Suspended");

Then, I copy the result and separate them by a comma then put them between the () in the following Query

update  timemanagement_Assignment 
set  timemanagement_Assignment.isFavoris_=false
where id_  in ();

When I try to execute this in one step using :

 update  timemanagement_Assignment 
    set  timemanagement_Assignment.isFavoris_=false
    where id_  in (Select A.id_ from
    timemanagement_Assignment as A
    Left join project_Task as T on T.id_ =A.task_id_
    Left join project_Phase as P on P.id_ =T. phase_id_
    Left join project_Iteration as I on I.id_ =P. iteration_id_
    Left join project_Lot as L on L.id_ =I. Lot_id_
    Left join project_Project as Pr on Pr.id_ =L. project_id_
    Where A.isfavoris_ =true and Pr.status_ in("Closed", "Archived","Suspended"));

I got the following error Error Code: 1093. You can't specify target table 'timemanagement_Assignment' for update in FROM clause I discovered that for some reason MySQL doesn't allow this type of queries. Could you please help me to find a way to re-write this in one step to put it in an event ? Thank you

Yes I'm pretty sure, I have been using this for a while an it work perfectly fine. — Rached Khalledi, Jan 18 '21 at 16:31

score 2 · Accepted Answer · answered Jan 18 '21 at 16:24

Perhaps multi table update is what you need https://dev.mysql.com/doc/refman/8.0/en/update.html

update  timemanagement_Assignment 
        Left join project_Task as T on T.id_ =A.task_id_
        Left join project_Phase as P on P.id_ =T. phase_id_
        Left join project_Iteration as I on I.id_ =P. iteration_id_
        Left join project_Lot as L on L.id_ =I. Lot_id_
        Left join project_Project as Pr on Pr.id_ =L. project_id_
set  timemanagement_Assignment.isFavoris_=false
Where A.isfavoris_ =true and Pr.status_ in("Closed", "Archived","Suspended"));

Yes thank you so much that works, but it seems like I have to set SQL_SAFE_UPDATES to 0, do you think this could have an impact ? — Rached Khalledi, Jan 19 '21 at 09:10

Creating a MySQL Query in one step beside of two

1 Answers1