CODEWITHSUNDEEP
sqlpostgresql
Max Yankov
Max Yankov

Reputation: 13327

A correct query produces a syntax error when used as a sub-query

First, I have this query, which works just fine:

DELETE FROM notifications
WHERE data @> '{"postID": 321}'
RETURNING user_id, read

But then, I use it as a sub-query:

SELECT d.user_id, count(d.user_id)
FROM (
    DELETE FROM notifications
    WHERE data @> '{"postID": 321}'
    RETURNING user_id, read
) as d
WHERE d.read = false
GROUP BY d.user_id

And get this error:

ERROR:  syntax error at or near "FROM"
LINE 16:  DELETE FROM notifications
                 ^
SQL state: 42601
Character: 245
  1. How do I fix this and achieve the intended result?
  2. How, in general, can a correct query become an incorrect sub-query?

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Use a CTE:

WITH d as (
      DELETE FROM notifications
      WHERE data @> '{"postID": 321}'
      RETURNING user_id, read
     ) 
SELECT d.user_id, count(d.user_id)
FROM d
WHERE d.read = false
GROUP BY d.user_id

Upvotes: 3

Related Questions