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?

Answer 1

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);