Re: delete query using CTE
| От | Roger Bos |
|---|---|
| Тема | Re: delete query using CTE |
| Дата | |
| Msg-id | CAPV07m__+v8HKFpc8w_Txw2HWXhg01cN2SeChVV-Kp5dfQEd2g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: delete query using CTE ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: