How to convert select row_number query to delete query on PostgreSQL?

Question

I have a query like this which is working well on postgresql server;

SELECT *
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

When I convert this to this query;

DELETE
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

It throws an error like this;

ERROR:  syntax error at or near "("
LINE 3:   (SELECT s_o_i,

why does it throw an error on delete query?

DELETE
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

I expect it to run and delete the records on the clauses.

You can't delete data from a query result. Deletion can be done on a table or on a cte. What exactly do you want to do? Could you please show some sample data and the expected result after the intended deletion? — Jonas Metzler
– Jonas Metzler, Commented Feb 9, 2023 at 8:22
I want to delete the rows with the same clauses that I used in select query because I need that rank value to delete the records. — Lasn Erhast
– Lasn Erhast, Commented Feb 9, 2023 at 8:26
Can you show me an example with CTE? Maybe I could use that rank with that — Lasn Erhast
– Lasn Erhast, Commented Feb 9, 2023 at 8:27
Ok, I would use a cte and delete from its result, see here a documentation: learn.microsoft.com/en-us/sql/t-sql/queries/… (I know, it's a documentation of SQL Server, but that functionality doesn't differ to Postgres) — Jonas Metzler
– Jonas Metzler, Commented Feb 9, 2023 at 8:30
Can you show me how to convert the select query that I wrote as an example? I don't know cte — Lasn Erhast
– Lasn Erhast, Commented Feb 9, 2023 at 8:38

Zegarek · Accepted Answer · 2023-02-09 09:16:53Z

Assuming that your aim is to remove all records from each group of (g_i, c) from more than a month ago, except for the latest/highest o_s: online demo

DELETE FROM b_o WHERE ctid in 
(   SELECT ctid
    FROM 
    (   SELECT  ctid,
                row_number() OVER w AS rank                             
        FROM b_o 
        WHERE m_t < CURRENT_DATE - interval '1 months'
        WINDOW w AS (PARTITION BY g_i, c ORDER BY o_s DESC)
    ) as alias
    WHERE rank > 1
);

You were trying to delete records from a subselect instead of your table b_o, which isn't syntactically correct and doesn't make much sense because the subquery table lives only for the duration of the outer query.
You referred to m_t column outside the subquery without selecting it there first, so it wouldn't be recognised.
If s_o_i column uniquely identifies records in the table, you can use it instead of ctid.
In this case common table expressions aren't necessary, but you can use one to flatten things a bit:

WITH cte AS 
(   SELECT  ctid,
            row_number() OVER w AS rank                             
    FROM b_o 
    WHERE m_t < CURRENT_DATE - interval '1 months'
    WINDOW w AS (PARTITION BY g_i, c ORDER BY o_s DESC)
)
DELETE FROM b_o WHERE ctid in 
(   SELECT ctid
    FROM cte
    WHERE rank > 1
);

Collectives™ on Stack Overflow

How to convert select row_number query to delete query on PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related