I am using postgreSQL 9.1 and I want to delete duplicates from my table using this tip: https://stackoverflow.com/a/3822833/2239537
So, my query looks like that:
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
But it shows me
ERROR: relation "cte" does not exist
SQL state: 42P01
Character: 157
However this statement works fine:
WITH cte
AS (SELECT ROW_NUMBER()
OVER (PARTITION BY code, card_id, parent_id
ORDER BY id DESC) RN
FROM merchantcard)
SELECT * FROM cte
WHERE RN > 1
Any ideas how to get it work? Thanks!
The PostgreSQL DELETE statement allows you to delete one or more rows from a table. In this syntax: First, specify the name of the table from which you want to delete data after the DELETE FROM keywords. Second, use a condition in the WHERE clause to specify which rows from the table to delete.
Description. DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.
Introduction to PostgreSQL DELETE JOIN. It is used to delete table records or rows. We can join two tables with “using” keyword in PostgreSQL, after table join it will delete the specified rows from the specified table. It is used to delete the rows using with and without where conditions.
that's because CTE in PostgreSQL works differently than CTE in SQL Server. In SQL Server CTE are like an updatable views, so you can delete from them or update them, in PostgreSQL you cannot.
you can join cte and delete, like:
with cte as (
select
id,
row_number() over(partition by code, card_id, parent_id order by id desc) as rn
from card
)
delete
from card
where id in (select id from cte where rn > 1)
On the other hand, you can write DDL statements inside CTE in PostgreSQL (see documentation) and this could be very handy. For example, you can delete all rows from card
and then insert only those having row_number = 1:
with cte1 as (
delete
from card
returning *
), cte2 as (
select
row_number() over(partition by code, card_id, parent_id order by id desc) as rn,
*
from cte1
)
insert into card
select <columns here>
from cte2
where rn = 1
I know, you are asking how you can solve your problem using the WITH statement, and got a good answer already. But I suggest looking at alternatives in the same question you linked.
What about this one?
DELETE FROM card
WHERE id NOT IN (
SELECT MIN(id) FROM card
GROUP BY code, card_id, parent_id
);
For me it worked Like this in Postgres/GreenPlum :
delete
from card where id in (
with cte as (
select
id,
row_number() over(partition by code, card_id, parent_id order by id desc) as rn
from card
)
select id from cte where rn > 1);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With