updating table with SELF JOIN results in "The target table ORDER_HEADER of the UPDATE" is not updatable

35 Views Asked by At

Basically put, In MySQL I'm attempting to update a flag on the premise that

  1. another flag is enabled for all given 'orders', and
  2. the oldest flag of said orders is greater than 7 days old.

There's a couple things I'm playing around with in the where clause, but I've been perusing and the structure of my code seems okay? but I'm getting hit with

ERROR: The target table ORDER_HEADER of the UPDATE is not updatable.

I've tried running the alias using AS, as well as moving things around. I'd like to continue to utilize a join instead of building a subquery as there's a couple Where requirements that bounce off each other. Any help would be appreciated.

UPDATE
    ORDER_HEADER AS OH
    JOIN (SELECT * FROM ORDER_HEADER) AS OTHEROH ON OH.SHIP_TO_LINE1 = OTHEROH.SHIP_TO_LINE1
        AND OH.SHIP_TO_LINE2 = OTHEROH.SHIP_TO_LINE2
        AND OH.SHIP_TO_CITY = OTHEROH.SHIP_TO_CITY
        AND OH.SHIP_TO_STATE_PROVINCE = OTHEROH.SHIP_TO_STATE_PROVINCE
        AND OH.SHIP_TO_POSTAL_CODE = OTHEROH.SHIP_TO_POSTAL_CODE
        
        
         SET OTHEROH.VERBAL_CONFIRMATION_NAME = 'true' AND OH.VERBAL_CONFIRMATION_NAME = 'true'
WHERE
    OH.NUMBER <> OTHEROH.NUMBER 
    AND OH.CURRENT_STATUS = 'New' 
    AND OTHEROH.CURRENT_STATUS = 'New'
    AND OH.TERMS_TYPE_PORT_OR_PLACE = 'true' 
    AND OTHEROH.TERMS_TYPE_PORT_OR_PLACE = 'true' 
    -- AND OTHEROH.VERBAL_CONFIRMATION_NAME is null
    -- AND OH.VERBAL_CONFIRMATION_NAME is null
    AND OTHEROH.CREATED_DATE <= NOW() - INTERVAL 1 WEEK 
    AND OH.CREATED_DATE >= OTHEROH.CREATED_DATE -- AND OH.CREATED_DATE between (NOW() - INTERVAL 1 WEEK AND NOW())
    AND OH.DELIVER_TO_NAME = OTHEROH.DELIVER_TO_NAME

0

There are 0 best solutions below