CODEWITHSUNDEEP
postgresqlwith-statement
Ivan Karotki
Ivan Karotki

Reputation: 342

How to reuse Postgres variables declared using 'WITH' operator

i need to delete records from two tables, but i cannot perform it consistently, because of after deletion from first table there will be no data to delete from second. I have tried following:

WITH person_ids AS(select person_id from application_person 
                              where application_id in (select DISTINCT duplicate.id 
                                    from application duplicate inner join application application
                                    on duplicate.document_id = application.document_id
                                    where duplicate.modify_date < application.modify_date)),
                                    
delete from application_person where application_person.person_id in (select person_id from person_ids);

delete from person where id in (select person_id from person_ids);

For second call of person_ids i have Query failed: ERROR: relation "person_ids" does not exist

What am i doing wrong? Thanks.

Upvotes: 0

Views: 194

Answers (1)

Bergi
Bergi

Reputation: 665364

What am i doing wrong?

You have two separate statements. The person_ids is only in scope within the first one, which lasts until the semicolon.

You'll want to use

WITH duplicate_applications AS (
  select DISTINCT duplicate.id 
  from application duplicate
  inner join application application using (document_id)
  where duplicate.modify_date < application.modify_date)
), deleted_persons AS (
  delete from application_person
  where application_id in (select application_id from duplicate_applications)
  returning person_id
)
delete from person
where id in (select person_id from deleted_persons);

Upvotes: 1

Related Questions