MySQL Update using a subquery on the updated table

table_subquery_update.sql

-- MySQL creates a lock on tables during updates, so a temporary table must be used by the inner query within the selection criteria. 

-- Update Workorder Steps to 'failure_int' datareading_type  
UPDATE workorder_steps 
SET    datareading_type_id = (SELECT id 
                              FROM   datareading_types 
                              WHERE  code = 'failure_int') 
WHERE  id IN (SELECT id 
              FROM   (SELECT workorder_steps.id 
                      FROM   workorders 
                             INNER JOIN workorder_types 
                                     ON workorders.workorder_type_id = 
                                        workorder_types.id 
                                        AND ( workorder_types.code = 
                                              'compliance' 
                                               OR workorder_types.code = 
                                                  'sch_compliance' ) 
                             INNER JOIN workorder_tasks 
                                     ON workorder_tasks.workorder_id = 
                                        workorders.id 
                             INNER JOIN workorder_steps 
                                     ON workorder_tasks.id = 
                                        workorder_steps.workorder_task_id 
                             INNER JOIN datareading_types 
                                     ON datareading_types.id = 
                                        workorder_steps.datareading_type_id 
                                        AND datareading_types.code = 'int') AS wo_step_temp); 


-- Update Steps to 'failure_int' Datareading Type  
UPDATE steps 
SET    datareading_type_id = (SELECT id 
                              FROM   datareading_types 
                              WHERE  code = 'failure_int') 
WHERE  id IN (SELECT id 
              FROM   (SELECT steps.id 
                      FROM   tasks 
                             INNER JOIN workorder_types 
                                     ON workorder_types.id = 
                                        tasks.workorder_type_id 
                                        AND ( workorder_types.code = 
                                              'compliance' 
                                               OR workorder_types.code = 
                                                  'sch_compliance' ) 
                             INNER JOIN steps 
                                     ON steps.task_id = tasks.id 
                             INNER JOIN datareading_types 
                                     ON steps.datareading_type_id = 
                                        datareading_types.id 
                                        AND datareading_types.code = 'int') AS step_temp);