使用不同条件的关系删除mysql

z18hc3ub 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(318)

+--------+---------------------+--------+------------+--------------------------------------------------------------+-------+------------+------+--------+-------+
| 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 |
+--------+------------+

我需要删除一个给定的行与电子邮件的观察员和电子邮件的被观察的人。
例如。
我收到电子邮件：“kacper@niemczyk.pl“和”elo@elo.pl“我必须把这些人从下表中删除。在本例中，删除userid=10和followerid=13的行
我知道可以通过电子邮件找到一个身份证，然后删除它，但我也知道，这可以更快地完成与关系的帮助，但我不知道如何。

mysql foreign-keys relational-database

来源：https://stackoverflow.com/questions/51398257/mysql-delete-using-relationships-with-different-conditions

1条答案

按热度按时间

lvjbypge1#

作为参考：

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

编辑评论：
将记录添加到 followers 同样，使用：

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

赞(0）回复(0）举报 2021-06-20

我来回答

使用不同条件的关系删除mysql

1条答案

相关问题

热门标签

最新问答