PostgreSQL with-delete“关系不存在”
发布时间:2020-12-13 16:16:30 所属栏目:百科 来源:网络整理
导读:我正在使用postgreSQL 9.1,我想使用这个提示从我的表中删除重复项: https://stackoverflow.com/a/3822833/2239537 所以,我的查询看起来像这样: WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY code,card_id,parent_id ORDER BY id DESC) RN FROM ca
我正在使用postgreSQL 9.1,我想使用这个提示从我的表中删除重复项:
https://stackoverflow.com/a/3822833/2239537 所以,我的查询看起来像这样: WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY code,card_id,parent_id ORDER BY id DESC) RN FROM card) DELETE FROM cte WHERE RN > 1 但它告诉我 ERROR: relation "cte" does not exist SQL state: 42P01 Character: 157 但是这个说法很好: WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY code,parent_id ORDER BY id DESC) RN FROM merchantcard) SELECT * FROM cte WHERE RN > 1 任何想法如何让它工作?
这是因为PostgreSQL中的CTE与SQL Server中的CTE不同.在SQL Server中,CTE就像一个可更新的视图,所以你可以从它们中删除或更新它们,在PostgreSQL中你不能.
你可以加入cte和delete,如: with cte as ( select id,row_number() over(partition by code,parent_id order by id desc) as rn from card ) delete from card where id in (select id from cte where rn > 1) 另一方面,您可以在PostgreSQL中的CTE中编写DDL语句(参见documentation),这可能非常方便.例如,您可以从卡中删除所有行,然后仅插入具有row_number = 1的行: with cte1 as ( delete from card returning * ),cte2 as ( select row_number() over(partition by code,parent_id order by id desc) as rn,* from cte1 ) insert into card select <columns here> from cte2 where rn = 1 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |