SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    delete entries from 2nd table which are not in first table

    Hi
    i have 2 tables users and userroles with common column 'externalid'
    in users tables i have deleted many entries which are no longer needed but userroles has 628 rows. i want to delete all rows in userroles with 'externalid' column values which do not exist in users.

    i did same thing 6 months ago but now i can't remember.

    help please
    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    DELETE userroles
      FROM userroles
    LEFT OUTER
      JOIN users
        ON users.externalid = userroles.externalid
     WHERE users.externalid IS NULL
    you might want to investigate declaring the externalid in userroles as a foreign key to the users table, with ON DELETE CASCADE option

    that way, when you delete a user, the related userroles row(s) are automatically also deleted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you very much.
    I know foreign key is better idea but i have no authority to change the tables.
    not a single table has foreign key constraint.

    only thing i allowed to do is that after 6 months empty all the tables and keep some users in user tables.

    next iteration is final iteration.
    they are considering to create new application from scratch with new database design.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •