delete the orphan record

[scvinodkumar]

hi

 

i have category and subcategory in the same table

 

previously i have not written the code to delete the subcategory while deleting the parent category.

 

so what happens now, all the subcategory stayed in the table even their parent is deleted.

 

now i want to delete those records only, how to do this through query?

 

please help me

 

 

[lonewolf217]

it would help if you posted your database structure so we have a clearer picture of your issue

[scvinodkumar]

yeah sure

 

this is the structure, here id is the category id and topicId is the parent id

 

CREATE TABLE IF NOT EXISTS `category` (

  `id` int(20) NOT NULL auto_increment,

  `title` varchar(255) NOT NULL,

  `content` text,

  `topicId` int(20) NOT NULL default '0',

  `status` varchar(50) NOT NULL default 'topic',

  `position` varchar(4) NOT NULL default '0',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

)

 

[sasa]

DELETE FROM `category` where `topicId` NOT IN (SELECT `id` FROM `category`)

[scvinodkumar]

wow what a simple query. thanks guru