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
- How do I fix this and achieve the intended result?
- How, in general, can a correct query become an incorrect sub-query?
Advertisement
Answer
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