-- 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);