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

Question

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?

score 20 · Accepted Answer · edited Jan 16 '17 at 06:32

20

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

edited Jan 16 '17 at 06:32

Gurwinder Singh

38,557
6
51
76

answered Oct 20 '13 at 20:38

Akash

1,716
2
23
43

It's works like a charm! ^^ Many thanks for the quite answer. I'll accept it as soon as I can :) – Airikr Oct 20 '13 at 20:41
@akash , I think this query is not applicable to tables that have huge number of data 'cause it's too slow. Can you suggest other query than these? Thanks. – slek Aug 20 '14 at 03:05
@slek Select the records to be deleted into a temp table, perform an outer join and delete the records where the ID is null.. might work, not sure – Akash Aug 20 '14 at 07:23
it will not work in access, I already tried that before. by the way thanks @Akash – slek Sep 02 '14 at 08:50
What if our tables have composite keys? Currently we use a table that is supposed to be a replica of a production table. Yet we do not do any deletes in case of data changes. Any advice? – Nerd in Training Apr 24 '15 at 18:02
@NerdinTraining something like `WITH CTE AS (SELECT c1,c2,c3 FROM types EXCEPT select c1,c2,c3 FROM types_photos ) DELETE types FROM types JOIN CTE c ON types.c1 = c.c1 AND types.c2=C=c2 AND types.C3=C.C3` should work, assuming c1,c2,c3 are your composite key columns – Akash Apr 25 '15 at 17:42

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

1 Answers1

Linked