Le 13/03/2022 à 15:44, Roger Bos a écrit :
Hello, trying to use CTE to remove duplicates from a table. The DELETE
version does not work, but the SELECT version does, so I am not
understanding what the problem is. Any suggestions on how to fix it?
Here is my query:
WITH cte AS
( SELECT *, RO
Thank you Michael & David for your extremely fast response. With your help
I was able to fix the query as follows:
DELETE FROM price_old
WHERE ctid IN
(SELECT ctid
FROM
(SELECT ctid,
ROW_NUMBER() OVER( PARTITION BY ticker, date
ORDER BY ctid ) AS my_row_num
On Sun, Mar 13, 2022 at 7:44 AM Roger Bos wrote:
> WITH cte AS
> ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY ticker,
> date) my_row_num FROM price_old)
> DELETE FROM cte WHERE my_row_num > 1;
>
> I get the following error:
>
> ERROR: relation "cte" does not exist LINE 3: DE
You can't delete rows that are in a CTE. You want to delete rows that are
in the table. Do you have a primary key that you can reference? Else, you
may need to reference the system column ctid.
Hello, trying to use CTE to remove duplicates from a table. The DELETE
version does not work, but the SELECT version does, so I am not
understanding what the problem is. Any suggestions on how to fix it?
Here is my query:
WITH cte AS
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORD
