Erik Edgren Erik Edgren - 4 months ago 9
SQL Question

Delete from table if the id doesn't exists in another table

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`)
)


So, my question is; how can I delete all id's from
types
that can't be found in
types_photos
?

Answer

DELETE FROM types WHERE id NOT IN (SELECT ID FROM types_photos)

Comments