SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Dunno
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how can I delete on these conditions?

    I want to delete all rows from a table where they don't match these conditions:

    delete from photos where username NOT "bob" AND "jennifer";

    Anyone have an idea what command I need to run from the mysql command-line to achieve this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you were pretty close

    tip: take a backup copy before running anybody's INSERT, UPDATE, or DELETE sql (SELECTs are safe to run)

    try this:
    Code:
    delete from photos 
     where NOT username in ('bob','jennifer')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Dunno
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you were pretty close

    tip: take a backup copy before running anybody's INSERT, UPDATE, or DELETE sql (SELECTs are safe to run)

    try this:
    Code:
    delete from photos 
     where NOT username in ('bob','jennifer')
    Nope...

    Query OK, 0 rows affected (0.09 sec)

    Checked phpMyAdmin and there are still other username rows in table "photos"
    agategod
    angele8
    etc.

    I want to delete all rows in table photos where the column "username" does not match bob or jennifer.


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whelp, whaddya know

    just another example of mysql's weird syntax

    tested it myself

    you have to do it this way:
    Code:
    delete from photos 
     where NOT ( username in ('bob','jennifer') )
    weird, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Dunno
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot mate for going to the trouble to help figure this one out. Your code worked a treated on our database and got the job done for me. MySQL is definitely weird.

  6. #6
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't have to be that weird. Here's the syntax I'd use:
    Code:
     delete from photos where username NOT IN ('bob','jennifer')
    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you know, i was wondering if anybody would notice that

    of course, that works too, and is actually easier to remember

    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
  •