Обсуждение: delete query using CTE
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 ORDER BY ticker, date) my_row_num FROM price_old)
DELETE FROM cte WHERE my_row_num > 1;
( 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: DELETE FROM cte WHERE my_row_num > 1;
But when I run change the query to a select query it runs fine (in that it returns all the duplicate rows). For example:
WITH cte AS
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY ticker, date) my_row_num FROM price_old)
SELECT * FROM cte WHERE my_row_num > 1;
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY ticker, date) my_row_num FROM price_old)
SELECT * FROM cte WHERE my_row_num > 1;
Sample output:
"US000000094541" "AAC" "2022-03-08 00:00:00-05" 9.75 9.76 9.75 9.75 100215 9.75 9.76 9.75 9.75 100215 0 1 2
"US000000094541" "AAC" "2022-03-09 00:00:00-05" 9.75 9.76 9.75 9.76 111334 9.75 9.76 9.75 9.76 111334 0 1 2
"US000000009823" "AAC" "2022-03-10 00:00:00-05" 9.75 9.76 9.74 9.74 170474 9.75 9.76 9.74 9.74 170474 0 1 2
"US000000090393" "ABCL" "2022-03-08 00:00:00-05" 8.19 8.545 7.81 8.22 1984348 8.19 8.545 7.81 8.22 1984348 0 1 2
"US000000094541" "AAC" "2022-03-09 00:00:00-05" 9.75 9.76 9.75 9.76 111334 9.75 9.76 9.75 9.76 111334 0 1 2
"US000000009823" "AAC" "2022-03-10 00:00:00-05" 9.75 9.76 9.74 9.74 170474 9.75 9.76 9.74 9.74 170474 0 1 2
"US000000090393" "ABCL" "2022-03-08 00:00:00-05" 8.19 8.545 7.81 8.22 1984348 8.19 8.545 7.81 8.22 1984348 0 1 2
Thanks,
Roger
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.
On Sun, Mar 13, 2022 at 7:44 AM Roger Bos <roger.bos@gmail.com> 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: DELETE FROM cte WHERE my_row_num > 1;
Right...when all is said and done DELETE removes rows from permanent tables. While "cte" does exist it is a virtual table and so doesn't qualify. A permanent relation named cte does not exist from which permanent data can be deleted.
See the following for ways to deal with duplicate removal on incorrectly constrained tables.
David J.
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
FROM price_old ) t
WHERE t.my_row_num > 1 );
WHERE ctid IN
(SELECT ctid
FROM
(SELECT ctid,
ROW_NUMBER() OVER( PARTITION BY ticker, date
ORDER BY ctid ) AS my_row_num
FROM price_old ) t
WHERE t.my_row_num > 1 );
On Sun, Mar 13, 2022 at 10:52 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Mar 13, 2022 at 7:44 AM Roger Bos <roger.bos@gmail.com> 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: DELETE FROM cte WHERE my_row_num > 1;Right...when all is said and done DELETE removes rows from permanent tables. While "cte" does exist it is a virtual table and so doesn't qualify. A permanent relation named cte does not exist from which permanent data can be deleted.See the following for ways to deal with duplicate removal on incorrectly constrained tables.David J.
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 *, 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: DELETE FROM cte WHERE > my_row_num > 1; > > But when I run change the query to a select query it runs fine (in that > it returns all the duplicate rows). For example: > > WITH cte AS > ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY > ticker, date) my_row_num FROM price_old) > SELECT * FROM cte WHERE my_row_num > 1; > > Sample output: > > "US000000094541" "AAC" "2022-03-08 00:00:00-05" 9.75 9.76 9.75 9.75 > 100215 9.75 9.76 9.75 9.75 100215 0 1 2 > "US000000094541" "AAC" "2022-03-09 00:00:00-05" 9.75 9.76 9.75 9.76 > 111334 9.75 9.76 9.75 9.76 111334 0 1 2 > "US000000009823" "AAC" "2022-03-10 00:00:00-05" 9.75 9.76 9.74 9.74 > 170474 9.75 9.76 9.74 9.74 170474 0 1 2 > "US000000090393" "ABCL" "2022-03-08 00:00:00-05" 8.19 8.545 7.81 8.22 > 1984348 8.19 8.545 7.81 8.22 1984348 0 1 2 > > Thanks, > Roger > As Michael Lewis says, you can't use delete from cte but you can build your request to do the work with something like (example from you original request) : WITH cte AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) my_row_num , ctid -- or pk or other unique identifier if exists FROM price_old ) DELETE FROM tmp24 WHERE ctid IN (SELECT ctid FROM cte WHERE my_row_num = 1); Benj