Delete unmatching records in the child table

• User7766

  SSCommitted

  Points: 1969

  More actions

  February 24, 2015 at 11:21 pm

  #398073

  Hello All,

  I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now. Can anyone please let me know the query?

  ResumeSkill Skill

  Id SkillId

  SkillId Name

  I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.

  Thanks,

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  February 24, 2015 at 11:40 pm

  #1779802

  maruthipuligandla (2/24/2015)

  ---

  Hello All,

  ResumeSkill Skill

  Id SkillId

  SkillId Name

  I want to delete all the records from ResumeSkill that don't have matching skillId in Skill table.

  Thanks,

  DELETE RS

  FROM ResumeSkill RS

  WHERE NOT EXISTS

  (SELECT 1 FROM SkillTable ST

  WHERE RS.Skill = ST.SkillID)

  Without DDL and data, this code is completely untested. First, convert the above statement to a Select to make sure it returns the rows you really want to delete. After you are satisfied that the Select is working, change it back to a delete and it should delete exactly the same number of rows as you saw in the Select.

  Test, test, test!

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• User7766

  SSCommitted

  Points: 1969

  More actions

  February 24, 2015 at 11:50 pm

  #1779803

  The select query worked, i've deleted the unmatching records earlier and when i ran this query it didnt return any rows..

  Thanks alot!