DELETE using CTEs does not seem to work

Question

I have the following query which uses multiple CTE's:

;with Test as (select plan_id, max("id") maxid
from Scheme
group by plan_id),
Rec as (select rh.* from Scheme rh, Test
where rh.plan_id = Test.plan_id
and rh.id = Test.maxid
and rh.event_id = 20)
delete from Scheme rh USING Rec
where rh.id = "Rec".id;

I am getting following error :

[Err] ERROR:  missing FROM-clause entry for table "Rec"
LINE 9: where rh.id = "Rec".id;

If I re-write the query using just one CTE, then the delete from "table" USING "CTE" syntax works. What is it that I am missing in the above query?

You know that the `;` is supposed to go at the **end** of the statement. It's useless at the beginning. — , Jan 24 '15 at 07:35
Always provide a table definition and your version of Postgres. Then we can see things like the primary key definition to suggest a better query (the trivial syntax error aside). — Erwin Brandstetter, Jan 24 '15 at 19:48

score 4 · Answer 1 · edited May 23 '17 at 12:14

The actual cause of the error is gratuitous capitalization combined with incorrect quoting:

...
delete from Scheme rh USING Rec
where rh.id = "Rec".id;

Unquoted identifiers are cast to lower-case in Postgres and effectively case-insensitve. If you double-quote, you need to match the case. "Rec" is not the same as Rec. Details:

Are PostgreSQL column names case-sensitive?

Your statement cleaned up:

WITH test AS (
   SELECT plan_id, max(id) AS id
   FROM   scheme
   GROUP  BY plan_id
   )
,  rec AS (
   SELECT s.*
   FROM   scheme s
   JOIN   test   t USING (plan_id, id)
   WHERE  s.event_id = 20
   )
DELETE FROM scheme s
USING  rec
WHERE  s.id = rec.id;

But this is rather inefficient. There are better ways to identify the row with the greatest id per plan_id.

Shorter and faster with DISTINCT ON and a subquery instead of the CTE:

DELETE FROM scheme s
USING (
   SELECT DISTINCT ON (plan_id)
          plan_id, id, event_id 
   FROM   scheme
   ORDER  BY plan_id, id DESC NULLS LAST
   ) r
WHERE  r.event_id = 20
AND    s.id = r.id;

Details for DISTINCT ON:

Select first row in each GROUP BY group?

score -3 · Accepted Answer · edited Jan 24 '15 at 07:35

-3

You can do this two ways.

Use IN operator to delete the records.

WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme
WHERE  id IN(SELECT a.id
             FROM   rec a);

Or by using join also you can do this but Using construct will have the common column between the two table's not the table name.

WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme join rec using(id);

edited Jan 24 '15 at 07:35

answered Jan 24 '15 at 04:55

Pரதீப்

91,748
19
131
172

The query I posted above is syntactically correct. The issue was because of navicat automatically inserting quotes(") around Rec in last line i.e. where rh.id = "Rec".id. Removing quotes fixed the query. Anyways thanks for your reply. The second approach looks cleaner. – Pawan Mishra Jan 24 '15 at 05:10
@PawanMishra ohh thank you for info am not that good in postgres – Pரதீப் Jan 24 '15 at 05:11
2

An `IN` construct on a big set (unlike SQL Server) is typically the least favorable for performance. The second query is invalid syntax for Postgres. And you did not address the actual problem in the question at all: invalid capitalization / quoting. Basically, this doesn't answer the question. – Erwin Brandstetter Jan 24 '15 at 20:02

DELETE using CTEs does not seem to work

2 Answers2