CODEWITHSUNDEEP
sqlpostgresql
okzoomer
okzoomer

Reputation: 336

DELETE rows with specific common column

Using PostgreSQL, the table sessions has columns id (PK), user_id and expire. I would like to delete rows with id = 'deleteme' but also expired sessions from the same person, namely whose user_id match the deleted row and expire < now().

The query that I found to be working is

WITH temp AS (
  DELETE FROM sessions 
  WHERE id = 'deleteme' 
  RETURNING user_id)
DELETE FROM sessions 
WHERE user_id IN (
 SELECT user_id from temp)
AND expire < now()

What did not work was

WITH temp AS (
  DELETE FROM sessions 
  WHERE id = 'deleteme'
  RETURNING user_id)
DELETE FROM sessions
WHERE user_id = temp.user_id
AND expire < now()

which has error "missing FROM-clause entry for table 'temp'"

Are there simpler queries that achieve the same effect as my first query?

EDIT: if there are ways to do this with joins please let me know as well for I am quite new to SQL and eager to learn. I just don't know if that will delete from the original table in addition to the joined table.

Upvotes: 2

Views: 61

Answers (1)

Chris Travers
Chris Travers

Reputation: 111

Here the error message is not exactly clear.

For delete queries you need to have a USING clause for your CTE.

So:

WITH temp AS (
  DELETE FROM sessions 
  WHERE id = 'deleteme'
  RETURNING user_id)
DELETE FROM sessions
USING temp
WHERE user_id = temp.user_id
AND expire < now()

Upvotes: 2

Related Questions