SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with delete query

    I'm trying to create a delete query but need a little help with the syntax. I have two tables, "cust_email" and "cust_lookup" and the two fields I'm working with are "order_id" and "number". I'm trying to delete every record in "cust_lookup" where the "order_id" in the "cust_lookup" table = the "order_id" in the cust_email table and where "number" = 'PROCESSING'. Your help is greatly appreciated.

    What I've got so far is:

    mysql_query("DELETE FROM cust_lookup WHERE number = 'PROCESSING' AND")
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation

  2. #2
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this (Not tested yet)

    mysql_query("DELETE FROM cust_lookup WHERE number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id")

  3. #3
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes sense, but for some reason the data is not being deleted - anyone else have any ideas?

    Quote Originally Posted by jaswinder_rana
    try this (Not tested yet)

    mysql_query("DELETE FROM cust_lookup WHERE number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id")
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation

  4. #4
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    make sure the same order_id exists in both tables. if not then it won't delete anything and the only thing else i would suspect is the data itself.

    also try this code in PHPMyAdmin (or anything like that you use)

  5. #5
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The order_id field exists in both tables and the data is the exact same between the tables. When running the query in phpmyadmin, it says "unknown table "cust_email"....any ideas? The table exists and is spelled correctly.

    Quote Originally Posted by jaswinder_rana
    make sure the same order_id exists in both tables. if not then it won't delete anything and the only thing else i would suspect is the data itself.

    also try this code in PHPMyAdmin (or anything like that you use)
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation

  6. #6
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think i made a mistake try this
    mysql_query("DELETE FROM cust_lookup,cust_email WHERE number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id") or die(mysql_error());

    use die() in your queries in PHP(unless you are using some advanced error checking code)
    with mysql_query() to let you know if the query sucessfully executed or not and if not then it'll show the error.

  7. #7
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not looking to delete anything from the cust_email table, only the cust_lookup table. I changed your query to:

    mysql_query("DELETE FROM cust_lookup WHERE number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id") or die(mysql_error());

    but it's returning the same error phpmyadmin gave me:

    Unknown table 'cust_email' in where clause

    Thanks for sticking with me...


    Quote Originally Posted by jaswinder_rana
    i think i made a mistake try this
    mysql_query("DELETE FROM cust_lookup,cust_email WHERE number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id") or die(mysql_error());

    use die() in your queries in PHP(unless you are using some advanced error checking code)
    with mysql_query() to let you know if the query sucessfully executed or not and if not then it'll show the error.
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation

  8. #8

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its difficult without knowing your actual database layout, but this is the ideal question for http://www.sitepoint.com/forums/forumdisplay.php?f=182

  9. #9
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doing this
    mysql_query("DELETE FROM cust_lookup,cust_email WHERE cust_lookup.number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id") or die(mysql_error());

    doesnot mean you are deleteing from both tables(and again i guessed this because in select we specify two table when we wanna select inter-tale data).

    give it a try. you got nothing to loose RIGHT?? (i am assuming you are testing it on test data)

  10. #10
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I tried it, but got this error:

    You have an error in your SQL syntax near 'cust_email WHERE cust_lookup.number = 'PROCESSING' AND cust_lookup.order_id=cust' at line 1
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation

  11. #11

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following might work
    Code:
    DELETE cust_lookup FROM cust_email, cust_lookup WHERE cust_lookup.number = 'PROCESSING' AND cust_lookup.order_id=cust_email.order_id

  12. #12
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes drzoid. your syntax is correct. i tested it.

  13. #13
    SitePoint Evangelist jimday1982's Avatar
    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    562
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all for your help - apparently I'm using an older version of mysql and it doesn't support subqueries, so I'm using the following and it works without a problem:

    PHP Code:
     //Delete "processing" records
     
    $sql "select * from cust_email";
     
    $result mysql_query($sql$link) or die(mysql_error());
     if (
    $myrow mysql_fetch_array($result)) {
      do {
       
    $order_id $myrow["order_id"];
       
    mysql_query("DELETE FROM cust_lookup WHERE order_id = '$order_id'") or die(mysql_error());
       } while (
    $myrow mysql_fetch_array($result));
     } 
    Jimmy Day
    Senior Systems Analyst
    Piedmont Healthcare Corporation


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
  •