MySQL DELETE using relationships with different conditions

Question

+--------+---------------------+--------+------------+--------------------------------------------------------------+-------+------------+------+--------+-------+
| UserID | email               | name   | surname    | password                                                     | place | birthDay   | male | female | admin |
+--------+---------------------+--------+------------+--------------------------------------------------------------+-------+------------+------+--------+-------+
|     10 | elo@elo.pl          | elo    | elo        | $2b$12$JPdUjCFv2mtoG3b2Dx0v1.D5089S4wUsg0aS21joLhlrzb1f4X3sW | stg   | 2000-12-09 |    1 |      0 |     1 |
|     11 | kacper@wp.pl        | kacper | kacper     | $2b$12$mlKWmIwdmj8Q0Py36H3m1O2REqYD9VBacmmk8jiogBIfrUmKy4XpG | stg   | 0000-00-00 |    1 |      0 |     0 |
|     12 | filip@galikowski.pl | filip  | galikowski | $2b$12$7cxOXVs/tHytGE/j0nA/s.wdxOherYlJf18F3EA/elvUblEN99pLy | stg   | 0000-00-00 |    1 |      0 |     0 |
|     13 | kacper@niemczyk.pl  | kacper | niemczyk   | $2b$12$YoSDXFc/t.jr5K8EFrY16OH8jom6kylCqvdFL7FfL2rdrO6hVzxCa | stg   | 0000-00-00 |    1 |      0 |     0 |
+--------+---------------------+--------+------------+--------------------------------------------------------------+-------+------------+------+--------+-------+

+--------+------------+
| UserID | FollowerID |
+--------+------------+
|     12 |         13 |
|     10 |         13 |
+--------+------------+

I need to delete a given row with the email of the observer and email of the observed person.

For example.

I got email: "kacper@niemczyk.pl" and "elo@elo.pl" and I have to remove these people from the follow table. In this example delete row with UserID = 10 and FollowerID = 13

I know that it is possible to find an ID via email and then delete it but I also know that it can be done faster with the help of relations but I do not know how.

You question doesn't make much sense? you can delete via the ID which is a pretty standard way or doing things? can you go into more detail about your other approach? — Ryan Gadsdon, Jul 18 '18 at 09:33
answer for your question [here](https://stackoverflow.com/questions/16335659/delete-a-row-with-relations-with-other-tables) — serezha93, Jul 18 '18 at 09:34

Michał Turczyn · Accepted Answer · 2018-07-18T10:28:23.877

0

Use this as a reference:

create table users (id int, name varchar(10));
insert into users values (1, 'Kacper'),(2, 'Krzysztof'), (3, 'Kuba');
create table followers (userid int, followerid int);
insert into followers values (1, 2), (1, 3);
--now we want to delete from followers records, where Krzysztof follows Kacper
delete from followers
where exists (select 1 from users
              where id = followers.userid and name = 'Kacper')--followed user
  and exists (select 1 from users
              where id = followers.followerid and name = 'Krzysztof');--follower

EDIT regarding comment:

To add records to followers in simialir manner, use:

insert into followers
select u1.id, u2.id from users u1
cross join users u2
where u1.name = 'Kacper' --followed user
  and u2.name = 'Krzysztof' --follower

edited Jul 18 '18 at 10:28

answered Jul 18 '18 at 09:38

Michał Turczyn

32,028
14
47
69

Thank for help! I almost understand the whole query, I only have to look at it a few times to be able to use it myself – Kubatekk Jul 18 '18 at 10:11
Could you please help me with adding to the table in the same way? (exactly the same instead of removing but adds) – Kubatekk Jul 18 '18 at 10:20
Thank you, it works! The problem is that in comparison to removal, I do not understand the action of adding ... Could you explain it to me theoretically? – Kubatekk Jul 18 '18 at 10:42
You join the table `users` with itself, getting all combinations of users. Then you filter out on what users you want to add specifying their names (e-mails in your case) in `where` clause. – Michał Turczyn Jul 18 '18 at 10:44
A little complicated but I will try to understand exactly in practice. Thank you very much for help! – Kubatekk Jul 18 '18 at 11:02
Why not `delete from followers where userid = (select id from users where name = 'Kacper') and followerid = (select id from users where name = 'Krzysztof');`? – Jon Heggland Jul 18 '18 at 12:18
@JonHeggland It's generally recommended to use `exists` in such situation. – Michał Turczyn Jul 18 '18 at 12:27
@MichałTurczyn Why? – Jon Heggland Jul 18 '18 at 12:36

MySQL DELETE using relationships with different conditions

1 Answers1