postgresql - 如何重用使用“WITH”运算符声明的 Postgres 变量

Question

我需要从两个表中删除记录，但我无法一致地执行它，因为从第一个表中删除后，将没有数据要从第二个表中删除。我试过以下：

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);

对于 person_ids 的第二次调用，我有Query failed: ERROR: relation "person_ids" does not exist

我究竟做错了什么？谢谢。

标签： postgresqlwith-statement

Answer 1

我究竟做错了什么？

您有两个单独的语句。person_ids仅在第一个范围内，持续到分号。

你会想要使用

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);