There is a request

UPDATE ClientPolicy SET endDate = ( select min(c.begDate) - INTERVAL 1 day from ClientPolicy c where ClientPolicy.client_id = c.client_id AND c.id > ClientPolicy.id ) WHERE exists ( select 1 from ClientPolicy c where ClientPolicy.client_id = c.client_id AND c.id > ClientPolicy.id ) AND (policyType_id BETWEEN 1 AND 2) AND deleted <> 1;

he gives an error:

Table is specified twice for both UPDATE and as a separate source for data.

Made subqueries with a temporal relation and self-action, self-rejoining does not work (1 picture), in the second picture without self-rejoining, does not see the outer table (obviously). How can I fix it? enter image description here :

  • You would tell what kind of logic is incorporated in this query ... to understand the text is unrealistic. In general, most likely, the request must be erased and rewritten from scratch. - Akina

1 answer 1

 UPDATE ClientPolicy SET endDate = ( select min(c.begDate) - INTERVAL 1 day from (SELECT * FROM ClientPolicy) c where ClientPolicy.client_id = c.client_id AND c.id > ClientPolicy.id ) WHERE exists (select 1 from (SELECT * FROM ClientPolicy) c where ClientPolicy.client_id = c.client_id AND c.id > ClientPolicy.id )AND (policyType_id BETWEEN 1 AND 2)AND deleted <> 1;

Resolved