Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

215

28 апреля 2018, 18:41

Есть запрос

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;

он выдает ошибку:

Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

Делал подзапросы с временным отношением и сэлфджоином, сэлфджоин не работает ( 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;

Решено