CODEWITHSUNDEEP
phpmysqlsql-delete
TecBrat
TecBrat

Reputation: 3729

MySQL delete row if id is found in another row

In a table where there is a subscriber_id and a letter_id, a subscriber might be subscribed to more than one letter. I want to unsubscribe people from a certain letter if they are subscribed to ANY other letter. I need help building a query. I've seen what I might call nested queries before but I'm not experienced in writing them.

I could probably do this in PHP by first querying like: SELECT subscriber_id FROM subscriptions WHERE letter_id=3 then run a foreach on the result and delete where a subscriber_id has more than one match, but I'd bet MySQL can do this a lot faster if I only knew how.

I tried to write pseudo code here, but I get hung up even on that.

In case it helps, each row also has its own id.

Upvotes: 3

Views: 356

Answers (4)

A P
A P

Reputation: 457

DELETE FROM subscriptions WHERE letter_id=3 AND subscriber_id IN (SELECT * FROM (SELECT subscriber_id FROM subscriptions WHERE letter_id<>3) AS x)

Upvotes: 1

Pavith Vihanga Goonethilake
Pavith Vihanga Goonethilake

Reputation: 1

I hope you are asking something like this,

DELETE FROM subscriptions 
WHERE subscriber_id = (SELECT subscriber_id FROM subscriptions WHERE letter_id=3);

Upvotes: 0

jdp
jdp

Reputation: 3516

Assuming your table has a Primary Key (i.e. id with auto increment):

DELETE n1 FROM `table` n1, `table` n2 WHERE n1.id > n2.id AND n1.`field` = n2.`field`

Where table is your table name, and field is the field to test.

I adapted my query from this answer. If your table doesn't have a primary ID table, you can add it to an existing table.

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

The query to figure out who has more than one "other" letter (in this case letter_id 3) goes like this:

SELECT subscriber_id
FROM subscriptions
WHERE letter_id <> 3
GROUP BY subscriber_id
HAVING COUNT(*) > 1

Join this to your delete query, add the letter_id = 3 condition, and you should be all set:

DELETE FROM subscriptions
INNER JOIN (
  SELECT subscriber_id
  FROM subscriptions
  WHERE letter_id <> 3
  GROUP BY subscriber_id
  HAVING COUNT(*) > 1
) MoreThan1 ON subscriptions.subscriber_id = MoreThan1.subscriber_id
WHERE letter_id = 3

Upvotes: 0

Related Questions