removing rows only if it's returned by another query

Question

I have the following two tables

CREATE TABLE message_log
(
  id integer,
  message text,
  from_id character varying(500),
  to_id character varying(500),
  match_id character varying(500),
  own_account boolean,
  reply_batch boolean DEFAULT false,
  insert_time timestamp with time zone DEFAULT now()
)


CREATE TABLE likes
(
  id integer,
  userid character varying(500),
  insert_time timestamp with time zone DEFAULT now()
)

I have the following query which returns match_ids if no message with the same match_id is sent that contains "@".

select distinct(match_id) from message_log where own_account = TRUE and match_id not in 
(select match_id from message_log where message like '%@%')

I want to also return the to_ids because they are needed in the query I want to construct so I modified the query to

select distinct(match_id, to_id) from message_log where own_account = TRUE and match_id not in 
(select match_id from message_log where message like '%@%')

Now I want to create a query that would delete any row in the likes table if the to_id returned from the above query matches the userid in the likes table. Is it possible to do this in one query?

delete from ... where to_id in (select ...). But distinct is NOT a function. Don't put column lists - in parentheses in Postgres it does not what you think it does. — user330315
– user330315, Commented Mar 27, 2016 at 21:57

Jeremy Armstrong · Accepted Answer · 2016-03-27 21:56:30Z

1

Something like this should work:

delete from b
from (
    select distinct match_id, to_id
    from message_log
    where own_account = TRUE and match_id not in (select match_id from message_log where message like '%@%')
) a inner join likes b on a.to_id = b.userid

Essentially, just take your results, and inner join on your likes table to determine what results to delete from the likes table.

answered Mar 27, 2016 at 21:56

Jeremy Armstrong

95510 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

removing rows only if it's returned by another query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related