SQL 查询:删除连接表中不存在的条目? [英] SQL query: Delete a entry which is not present in a join table?
问题描述
我将删除所有没有订阅的用户,但每次尝试检测用户时似乎都会遇到问题.
I’m going to delete all users which has no subscription but I seem to run into problems each time I try to detect the users.
我的架构如下所示:
Users = {userid, name}
Users = {userid, name}
Subscriptionoffering = {userid, subscriptionname}
Subscriptionoffering = {userid, subscriptionname}
现在,我要做的是删除 user 表中的所有用户,subscriptionoffering 表中的计数为零.或者换句话说:所有用户 ID 不存在于订阅提供表中的用户.我尝试了不同的查询,但没有结果.
Now, what I’m going to do is to delete all users in the user table there has a count of zero in the subscriptionoffering table. Or said in other words: All users which userid is not present in the subscriptionoffering table. I’ve tried with different queries but with no result.
我试着说 where user.userid <>subscriptionoffering.userid
,但这似乎不起作用.有人知道如何创建正确的查询吗?
I’ve tried to say where user.userid <> subscriptionoffering.userid
, but that doesn’t seem to work. Do anyone know how to create the correct query?
谢谢
梅斯蒂卡
推荐答案
您可以使用带有 left outer join
的多表 delete
语句并专注于 不匹配的行像这样:
You can use a multi-table delete
statement with a left outer join
and focus on the non-matching rows like this:
delete u from Users as u
left outer join Subscriptionoffering as so
on so.userid = u.userid
where so.userid is null;
这里有一些测试代码来证明:
Here is some test code to prove it:
mysql> create table Users (userid int unsigned primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.43 sec)
mysql> create table Subscriptionoffering (userid int unsigned not null, subscriptionname varchar(32) not null, foreign key (userid) references Users(userid)) engine = innodb;
Query OK, 0 rows affected (0.41 sec)
mysql> insert into Users () values (), (), (), (), ();
Query OK, 5 rows affected (0.38 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from Users;
+--------+
| userid |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+--------+
5 rows in set (0.00 sec)
mysql> insert into Subscriptionoffering (userid, subscriptionname) values (1, 'One'), (3, 'Three'), (5, 'Five');
Query OK, 3 rows affected (0.31 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from Subscriptionoffering;
+--------+------------------+
| userid | subscriptionname |
+--------+------------------+
| 1 | One |
| 3 | Three |
| 5 | Five |
+--------+------------------+
3 rows in set (0.00 sec)
mysql> delete u from Users as u
-> left outer join Subscriptionoffering as so
-> on so.userid = u.userid
-> where so.userid is null;
Query OK, 2 rows affected (0.36 sec)
mysql> select * from Users;
+--------+
| userid |
+--------+
| 1 |
| 3 |
| 5 |
+--------+
3 rows in set (0.00 sec)
这篇关于SQL 查询:删除连接表中不存在的条目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!