MySQL 5.7 Update Error: 1093

Question

I have this update statement in a trigger in one of my Databases:

UPDATE trainees 
SET rsi_total = (
  SELECT SUM(RsiTotal) as RsiTotal 
  FROM (
    SELECT MAX(hours) as RsiTotal 
    FROM courses 
      LEFT JOIN do_not_add ON courses.fk_class_id = do_not_add.fk_class_id 
      INNER JOIN trainees ON courses.FK_TRAINEES_ID = trainees.PK_TRAINEE_ID 
    WHERE do_not_add.fk_class_id IS NULL 
      AND trainees.pk_trainee_id = new.fk_trainees_id 
    GROUP BY courses.FK_CLASS_ID
  ) courses
) 
WHERE trainees.pk_trainee_id = new.fk_trainees_id

and does exactly what I expect, recently I migrated my database to another machine and moved and renamed a few tables and columns. So I re-wrote this trigger to:

UPDATE main 
SET rsi_total = (
  SELECT SUM(RsiTotal) as RsiTotal 
  FROM (
    SELECT MAX(completed_hrs) as RsiTotal 
    FROM courses 
      LEFT JOIN jac.do_not_add ON courses.fk_class_id = do_not_add.fk_class_id 
      INNER JOIN main ON courses.fk_main_id = main.pk_main_id 
    WHERE do_not_add.fk_class_id IS NULL 
      AND main.pk_main_id = new.fk_main_id 
    GROUP BY courses.FK_CLASS_ID
  ) courses
) 
WHERE main.pk_main_id = new.fk_main_id

But I get Error 1093? I looked up this error and it says I can not update a table that is being changed, what I dont understand is why does the old trigger continue to work but not this new?

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

Migrating to another machine, you probably got a more recent version of MySql, where the optimizer doesn't accept any longer subqueries to access the same table which is being updated.

Look at this detailed answer.

edited May 23 '17 at 12:31

Community

1
1

answered Feb 24 '16 at 23:52

cFreed

4,404
1
23
33

Thank you for that link, Im still not sure how to proceed in order to correct the issue. The link did mention using SET optimizer_switch = 'derived_merge=off'; Dont know what it means yet so some more research is in order. – Gustavo1478 Feb 25 '16 at 00:27
Btw, my old machine was running MySQL 5.6, My new is running 5.7 and apparently its a change in the way MySQL functions that prevents it from working. – Gustavo1478 Feb 25 '16 at 00:29
@Gustavo1478 Your last comments enforces certainty about where the issue comes from. Regarding your previous comment, it's pretty easy: you merely have to add the `SET optimizer_switch = 'derived_merge=off';` line at the beginning of your query. – cFreed Feb 25 '16 at 00:32
If another SO topic is the straight answer to a question, then flag it as duplicate next time instead of providing an answer! – Shadow Feb 25 '16 at 06:09
@Shadow As a general rule, I agree. But here the OP stated the peculiar case of machine migration, which needed the explanation about a probable version change. It's why I found the other topic was not "the straight answer" to the question. – cFreed Feb 25 '16 at 12:41

MySQL 5.7 Update Error: 1093

1 Answers1