时间戳

首页 > 解决方案 > 在以下语句完成之前未提交 CTE 删除

sql - 在以下语句完成之前未提交 CTE 删除

问题描述

我遇到的问题是已删除的数据稍后仍会出现在同一查询中。自然，在完全独立的查询中，被删除的数据不会出现。

这不是我的用例，但我认为这是显示问题的最简单方法：

CREATE TABLE company (id INT PRIMARY KEY, name TEXT);
CREATE TABLE employee (id INT PRIMARY KEY, company_id INT REFERENCES company(id), name TEXT);

INSERT INTO company VALUES (1, 'first company');
INSERT INTO company VALUES (2, 'second company');

INSERT INTO employee VALUES (1, 1, 'first employee');
INSERT INTO employee VALUES (2, 2, 'second employee');

-- this select can successfully query for the data which has just been deleted
WITH deleted_employee AS (DELETE FROM employee WHERE id = 1 RETURNING id)
SELECT id, name FROM employee JOIN deleted_employee USING (id);

-- this select shows it has been deleted
SELECT * FROM employee;

我在这里把它变成了小提琴。

似乎DELETE直到整个查询完成才提交 just，这感觉很奇怪，因为优先级DELETE要求SELECT.

有没有办法在单个查询中实现这一点？

编辑

答案已经回答了直接问题。根本问题是删除员工，然后删除其关联公司，如果没有更多员工与该公司关联。

这是我虽然可以解决问题的查询：

WITH affected_company AS (DELETE FROM employee WHERE id = 1 RETURNING company_id)
DELETE FROM company
USING affected_company
WHERE NOT EXISTS (
  SELECT 1
  FROM employee
  WHERE company_id = affected_company.company_id
);

SELECT * FROM company;
SELECT * FROM employee;

和一个更新的小提琴。

您可以看到该公司没有被删除。

标签： sqlpostgresqlcommon-table-expressionsql-delete

解决方案

这是预期的并记录在案。

从手册中引用

WITH 中的子语句彼此同时执行，并与主查询同时执行。因此，当在 WITH 中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用同一个快照执行（参见第 13 章），因此它们无法“看到”彼此对目标表的影响。这减轻了行更新的实际顺序的不可预测性的影响，并且意味着返回数据是在不同的 WITH 子语句和主查询之间传达更改的唯一方法

（强调我的）

您可以使用链式 CTE 删除公司：

with deleted_emp as (
  delete from employee 
  where id = 1 
  returning company_id, id as employee_id
)
delete from company
where id in (select company_id from deleted_emp) 
  and not exists (select * 
                  from employee e
                     join deleted_emp af 
                       on af.company_id = e.company_id 
                      and e.id <> af.employee_id)

从not exists子查询中排除刚刚删除的员工很重要，因为这将始终在第二个删除语句中可见，因此不存在永远不会是真的。因此，子查询实质上检查是否有除已删除员工之外的员工分配给同一公司。

在线示例：https ://rextester.com/IVZ78695

推荐阅读