本文介绍了如果 id 不存在于另一个表中,则从表中删除的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从 types
中删除在 types_photos
中找不到的 ID,但我不知道如何实现.types_photos
中的 id_type
与 types
中的 id
相同.表的结构如下所示:
I want to delete the id's from types
that can't be found in types_photos
but I don't know how I can accomplish this. id_type
in types_photos
are the same as id
in types
. Here's how the table's structure looks like:
CREATE TABLE IF NOT EXISTS `types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_user_added` int(11) DEFAULT '0',
`id_user_edited` int(11) DEFAULT '0',
`data_name` text NOT NULL,
`data_name_seo` text NOT NULL,
`data_type` enum('tag','equipment','search') NOT NULL,
`datetime_added` datetime NOT NULL,
`datetime_edited` datetime NOT NULL,
`ipaddress_added` text NOT NULL,
`ipaddress_edited` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)
CREATE TABLE IF NOT EXISTS `types_photos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_user_added` int(11) DEFAULT '0',
`id_user_edited` int(11) DEFAULT '0',
`id_type` int(11) DEFAULT '0',
`id_photo` int(11) DEFAULT '0',
`datetime_added` datetime NOT NULL,
`datetime_edited` datetime NOT NULL,
`ipaddress_added` text NOT NULL,
`ipaddress_edited` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)
所以,我的问题是;如何从 types
中删除所有在 types_photos
中找不到的 ID?
So, my question is; how can I delete all id's from types
that can't be found in types_photos
?
推荐答案
DELETE FROM types
WHERE id NOT IN (
SELECT ID FROM types_photos
)
这篇关于如果 id 不存在于另一个表中,则从表中删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!