MySQL delete row if id is found in another row

Question

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.

An honest and well-worded question deserves an answer ;-) Unfortunately I don't know how (yet) but I'm sure someone will be kind enough to help out. — Funk Forty Niner, Oct 03 '13 at 19:15
if they cant be subscribed to more than one, you should have a unique key on (subscriber_id,letter_id) then the issue could never arise — , Oct 03 '13 at 19:18
@Dagon, They can subscribe to multiple letters. This letter is an exception. It's too general. Once they are subscribed to at least one specific letter, I need to take them out of the general one. The database is part of an application and my script is interacting with it in a way that the developer probably did not expect, so I need to be very careful about what changes I make. — TecBrat, Oct 03 '13 at 19:22

score 1 · Accepted Answer · edited Oct 03 '13 at 20:17

1

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

edited Oct 03 '13 at 20:17

Sliq

15,937
27
110
143

answered Oct 03 '13 at 19:27

Alexandr Perfilov

457
3
7

1

**Delete - I can't specify target table** http://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table – M Khalid Junaid Oct 03 '13 at 19:30
This looks like it's probably the right one. I'll test it carefully and let you know. :) Thanks. – TecBrat Oct 03 '13 at 19:32
1

@dianuj I did get that error. Thanks for the link. I'll see if I can make it work. – TecBrat Oct 03 '13 at 19:48
1

I didn't exactly follow why, but if you wrap the sub-query in `(select * from` and `as x)` then it works. – TecBrat Oct 03 '13 at 20:14

score 0 · Answer 2 · answered Oct 03 '13 at 19:24

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

score 0 · Answer 3 · edited May 23 '17 at 11:51

0

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.

edited May 23 '17 at 11:51

Community

1
1

answered Oct 03 '13 at 19:25

jdp

3,446
2
30
54

score 0 · Answer 4 · answered Oct 03 '13 at 19:28

0

I hope you are asking something like this,

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

answered Oct 03 '13 at 19:28

Pavith Vihanga Goonethilake

1
1

**Delete - I can't specify target table** http://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table – M Khalid Junaid Oct 03 '13 at 19:30

MySQL delete row if id is found in another row

4 Answers4