SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete Using Join

    I'm trying to delete all fields in a table called profile where my fk to a related table called users does not exist.

    My query:

    DELETE FROM profile INNER JOIN users on profile.name=users.username where users.username !=admin

  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)
    we have a MySQL forum, and a Databases forum for every other database besides MySQL

    since you posted in the Databases forum, could you tell us which database you are using?

    the joined delete syntax varies, and depends on which one it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, this question would've have been more appropriately placed in the mysql forum. But yes, I am using mysql. Thank you for any answers.

  4. #4
    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)
    okay, no prob

    you wrote
    Code:
    users.username !=admin
    is admin a column?

    if it's supposed to be a string value, it should be in quotes

    in any case, the joined delete syntax is a bit different from the single table delete syntax, the FROM keyword goes in a different spot...

    but i can't fix it until you clear up what "admin" means
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    admin is a string. I forgot the quotes

  6. #6
    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)
    and the delete query, as you wrote it, doesn't work, correct?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, this syntax isn't working for me.

    I did find a different error in my query though. Column profile.name was a column I added after the db had begun being populated so the admin account didn't have a value for the profile name field.

    So instead I've tried the join on the id field. Though I'm still generating a syntax error:

    DELETE FROM profile INNER JOIN users ON users.id=profile.user_id WHERE users.username !='admin'

  8. #8
    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)
    i realize the manual isn't as clear as it could be
    Code:
    DELETE profile 
      FROM users 
    INNER 
      JOIN profile 
        ON profile.user_id = users.id
     WHERE users.username <> 'admin'
    as with all code that might change the content of your tables, make sure you take a backup first before running this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the query is running successfully now. But it's not deleting any records.

    I created a test account and was able to delete it by targeting it directly with an equality check

    WHERE users.username = 'testdelete'

    rather than delete everything not equal with inequality check we've been using

    WHERE users.username <> 'testdelete'

    I've also tried the != operator with no success.

    If memory serves, I've read certain joins may not return the expected matches, so I've tried inner, outer, right and left. But no luck here either.

  10. #10
    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)
    it's gotta be a data problem

    your problem can easily be resolved if you would kindly take the time to prepare a test case -- a few rows of test data in ~both~ tables -- and then dump the tables so that we get both the CREATE TABLE and the INSERT statements so that we can test things ourselves
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, I think I know what the problem is. I'd already cleared the users table except for the admin row.

    I thought I'd already tested to rule this out as our problem.

    I guess we can't run an equality check against records that don't exist.

    So this would bring me to my next question, how to remove orphaned data that doesn't have any records in a related table?

  12. #12
    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)
    Quote Originally Posted by maestro360 View Post
    ... how to remove orphaned data that doesn't have any records in a related table?
    could you please be a bit more specific? which tables? how are they related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you please be a bit more specific? which tables? how are they related?
    Each time a user registers that have their data entered into both the users table and the profile table. There's an fk of user_id in the profile table that relates to the user's id in the users table.

    Being I'd already cleared my users table except for my admin account I couldn't delete rows in the profile based on our inequality check:

    WHERE users.username <> 'admin'

    Or at least this my guess as to why the query wasn't working.... Perhaps there's a better explanation.

    After learning the flaw in my logic I found the EXIST/NOT EXIST operators. While researching these I found a perhaps even simpler method to delete the old data in the profile table:

    DELETE profile FROM profile
    LEFT JOIN users
    ON profile.user_id = users.id
    WHERE users.id IS NULL

    Looks like you helped someone with a similar problem back in 2006:
    http://www.sitepoint.com/forums/mysq...-x-410112.html

  14. #14
    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)
    Quote Originally Posted by maestro360 View Post
    Looks like you helped someone with a similar problem back in 2006:
    http://www.sitepoint.com/forums/mysq...-x-410112.html
    old doesn't always mean out of date, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy - you're freaking awesome

    Oh, and I love your book, too, though as you might have seen me post - I've been rather jumping the chapters.

    Best,
    Greg

  16. #16
    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)
    greg, thanks for the very kind words, you've made my day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •