我在 Postgres 中使用 CTE 编写了以下查询。现在我无法从中删除记录。
WITH cte AS (
SELECT
firstname,
lastname,
country,
ROW_NUMBER() OVER (
PARTITION BY
firstname,
lastname,
country
) row_num
FROM
employee
)
delete from cte
where row_num >1
当我运行此查询时,它显示错误:
关系“cte”不存在
这是我的表“员工”的示例
id firstname lastname country
1 "Raj" "Gupta" "India"
2 "Raj" "Gupta" "India"
3 "Mohan" "Kumar" "USA"
4 "James" "Barry" "UK"
5 "James" "Barry" "UK"
6 "James" "Barry" "UK"
无法从 CTE 中删除。
你可以这样做:
DELETE FROM employee
WHERE id IN (
SELECT
id
FROM (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
FROM
employee
) s
WHERE row_num > 1
)
如果您仍然想使用 CTE,则可以将子查询移至其中:
WITH cte AS (
SELECT
id
FROM (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
FROM
employee
) s
WHERE row_num > 1
)
DELETE FROM employee
WHERE id IN (SELECT * FROM cte)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)