CODEWITHSUNDEEP
sqlpostgresql
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 897

Postgres - Selecting a row after RETURNING multiple rows

My query is 

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning parent_id, comment_id, thread_id

And it returns 3 rows

null | 1 | 1BZbAR
1    | 2 | 1BZbAR
2    | 3 | 1BZbAR

How do I select the row where parent_id is null?

I have tried: 

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning MIN(parent_id), comment_id, thread_id

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning HAVING parent_id is null, comment_id, thread_id

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning parent_id, comment_id, thread_id as t
SELECT * FROM t WHERE parent_id is null

However all of them give them syntax errors. What should I do?

Upvotes: 1

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You should be able to subquery your current delete statement, or put it into a CTE and query that:

WITH cte AS (
    DELETE
    FROM event_comments
    WHERE thread_id = '1BZbAR'
    RETURNING parent_id, comment_id, thread_id
)

SELECT parent_id, comment_id, thread_id
FROM cte
WHERE parent_id IS NULL;

Upvotes: 2

Related Questions