0

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?

1 Answer 1

0

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 USING clause, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.