psql delete using join and return values



  • Hello I have this query:

    DELETE FROM c_table as c
        USING 
             a_table as a,
             b_table as b
        WHERE 
            a.id = c.id 
            AND b.a_id = c.id
        RETURNING a.*, b.*;
    

    but I have a problem, I need all the rows of table B that have the a_id, using this query I'm just returning the first one

    I don't know how I could do this, could anyone help me?

    would I need to use two queries? first delete and then select with join?



  • I need all the rows of table B that have the a_id

    Indicates a 1:n relationship between table A and B, and a 1:1 relationship between C and A.

    There is no join condition between table A and B in the https://www.postgresql.org/docs/current/sql-delete.html , which makes it an unconditional CROSS JOIN (Cartesian Product). The WHERE conditions connect rows via table C, not sure the planner will optimize that perfectly. I'd rather join A and B directly to exclude redundant rows early.

    Even then, the query proposes the same row in C for deletion multiple times (once for every related row in B). Reduce that to distinct c.id. One way is to use EXISTS instead of a JOIN.

    And yes, first delete and then select with join:

    WITH del AS (
       DELETE FROM c_table AS c
       USING  a_table a
       WHERE  c.id = a.id
       AND    EXISTS (SELECT FROM b_table b WHERE b.a_id = c.id)
       RETURNING c_id
       )
    SELECT b.*
    FROM   del d 
    JOIN   b_table b ON b.a_id = d.id;
    

    Obviously, this only returns rows from B where the related row in C has been deleted.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2