MySQL - How to Refer to Table in UPDATE Clause in Multiple Levels of Subqueires

Question

I am having trouble getting the following query to work...

/*SET APPROPRIATE DATABASE CONTEXT*/
USE IDMAS_VESSELS;

/*BEGIN UPDATE SCRIPT*/
UPDATE INSPECTION i SET i.WALL_LOSS = 
    CASE WHEN i.STATUS IN('A','R')
         THEN CASE WHEN i.INSPECTION_NO =   (
                                            SELECT INSPECTION_NO 
                                            FROM (SELECT * FROM INSPECTION) AS insp
                                            /*CAN REFER TO TABLE IN UPDATE CLAUSE*/
                                            WHERE EQUIPMENT_ID = i.EQUIPMENT_ID AND CML_ID = i.CML_ID 
                                            ORDER BY INSPECTION_DATE ASC, INSPECTION_NO
                                            LIMIT 1
                                            )
                   THEN i.NOMINAL_WALL_THICKNESS - i.MINIMUM_REMAINING_WALL_THICKNESS
                   ELSE     (
                            SELECT  MINIMUM_REMAINING_WALL_THICKNESS
                            FROM    (
                                    SELECT @rank:=@rank+1 AS DATE_POSITION, INSP.*
                                    FROM    (SELECT * FROM INSPECTION) AS insp
                                    /*CODE FAILS AT THIS LINE*/
                                    /*CAN NOT REFER TO TABLE IN UPDATE CLAUSE*/
                                    WHERE insp.EQUIPMENT_ID = i.EQUIPMENT_ID AND insp.CML_ID = i.CML_ID
                                    ) as DATA_WITH_DATE_POSITIONS
                            WHERE DATE_POSITION = 1
                            ) - i.MINIMUM_REMAINING_WALL_THICKNESS
              END
        ELSE NULL
    END
WHERE i.EQUIPMENT_ID = '%';

Upon running I get the following error:

Error Code: 1054. Unknown column 'i.EQUIPMENT_ID' in 'where clause'

I have concluded that MySQL will not let you refer to the table in the UPDATE Clause when it is embedded within multiple sub queries. However, you can refer to it when embedded in only one sub query (as identified in the notes within the code above).

So my question is how do I refer to the table in the UPDATE clause when it is embedded in multiple sub queries?

Possible solution: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause?rq=1 — Cine, Oct 18 '16 at 02:29
Thanks Cine, this methodology is much preferable to approach I was taking (multiple levels of sub queries) and solves my issue. Ashok's response is also in accordance with this and I have marked his response as the solution. — Josh, Oct 18 '16 at 05:08

score 1 · Accepted Answer · edited May 23 '17 at 12:02

I have updated your code please see below. This should now work as you intend. Update format has been based off the following link...

MySQL Error 1093 - Can't specify target table for update in FROM clause

-- SET REQUIRED VARIABLE DEFAULTS
SET @prev_equip := null;
SET @prev_cml := null;
SET @prev_rwt := null;
SET @cnt := 1;

-- 1. WALL LOSS
UPDATE INSPECTION i 
-- JOIN QUERIES TO MATCH INSPECTION RECORDS WITH ALL DATA TO BE MERGED/UPDATED TO INSPECTION TABLE
LEFT JOIN   (
            -- CREATE VIRTUAL INSPECTION TABLE WITH NEW RANKING AND PREVIOUS MIN RWT COLUMNS
            SELECT  IF(@prev_equip = ORDERED_DATA.EQUIPMENT_ID AND @prev_cml = ORDERED_DATA.CML_ID, @cnt := @cnt + 1, @cnt := 1) AS DATE_POSITION
                    , (@prev_rwt) AS PREVIOUS_MIN_RWT
                    , ORDERED_DATA.*
                    -- RE-EVALUATING VARIABLES BASED ON CURRENT ROW
                    , @prev_equip := ORDERED_DATA.EQUIPMENT_ID
                    , @prev_cml := ORDERED_DATA.CML_ID
                    , @prev_rwt := ORDERED_DATA.MINIMUM_REMAINING_WALL_THICKNESS
            FROM    (
                    -- ORDER DATA BY INSPECTION_DATE
                    SELECT EQUIPMENT_ID, CML_ID, INSPECTION_NO, MINIMUM_REMAINING_WALL_THICKNESS
                    FROM INSPECTION 
                    ORDER BY EQUIPMENT_ID, CML_ID, INSPECTION_DATE ASC, INSPECTION_NO
                    ) ORDERED_DATA
            ) R1 ON i.EQUIPMENT_ID = R1.EQUIPMENT_ID AND i.CML_ID = R1.CML_ID AND i.INSPECTION_NO = R1.INSPECTION_NO
SET i.WALL_LOSS = 
    CASE WHEN i.STATUS IN('A','R')
         THEN CASE WHEN R1.DATE_POSITION = 1
                   THEN i.NOMINAL_WALL_THICKNESS - i.MINIMUM_REMAINING_WALL_THICKNESS
                   ELSE R1.PREVIOUS_MIN_RWT - i.MINIMUM_REMAINING_WALL_THICKNESS
              END
         ELSE NULL
    END
WHERE i.EQUIPMENT_ID = '%';

Sorry for posing image instead of code. The editor was not allowing me to post the code. — Ashokkumar M. Prajapati, Oct 18 '16 at 02:32
I have updated your response with the source code I created using your methodology and a link to the theory behind the answer (as posted by Cine) — Josh, Oct 18 '16 at 05:18
Thanks for updates. Its a good example of mysql power utilization. Even we can join multiple tables and update all in **SET** clause at the same time. MySQL provides good options for update statement.. — Ashokkumar M. Prajapati, Oct 18 '16 at 16:50

MySQL - How to Refer to Table in UPDATE Clause in Multiple Levels of Subqueires

1 Answers1