Reputation: 1
Basically put, In MySQL I'm attempting to update a flag on the premise that
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
Upvotes: 0
Views: 29