Help!! Simple MySQL query

Discussion in 'MySQL' started by YIAM, Jul 31, 2008.

0
  1. #1
    Let I have two tables:

    Table one: have tow columns:

    id | name

    Table two:     have tow columns:

    id | name_id

    I want to delete all records from 2nd table whose name_id is not present in table one's id column.

    Can anyone help me in this.

    Thanks in advance.
     
    YIAM, Jul 31, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    This should work for you. Always make sure you have a backup in case you do get some poor advice...

    DELETE FROM table_2 WHERE name_id NOT IN (SELECT id FROM table_1)
     
    jestep, Jul 31, 2008 IP
    YIAM likes this.
  3. andrewgjohnson

    andrewgjohnson Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #3
    jestep's query will work for you - just a note to add: if you want to build in protection so this won't happen again you should add a FOREIGN KEY to table_2.
     
    andrewgjohnson, Jul 31, 2008 IP
    YIAM likes this.
  4. strikefreedom

    strikefreedom Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    yes, should be this
     
    strikefreedom, Jul 31, 2008 IP