如果 id 不存在于另一个表中，则从表中删除 [英] Delete from table if the id doesn't exists in another table

问题描述

我想从 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 不存在于另一个表中，则从表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！