Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts

    difficult update involving multiple tables

    Hi All,

    Can this be done?
    I'm trying (and failing miserably) to construct a query to update the FIELD 'invalid_email' for all records in a TABLE 'contacts' where the value of the FIELD 'email1' (email address) can be found contained in TABLE 'emails' in the middle of a blob of text in FIELD 'description' where the FIELD 'name' contains the string 'failure' without regard for case.

    TABLE 1: contacts
    FIELDS:
    1: invalid_email (type=bool)
    2: email1 (type= varchar)

    TABLE 2: emails
    FIELDS:
    1:name (type=varchar)
    2:description (type=text)

    My feeble attempt to write the query

    Code:
    UPDATE 'contacts' SET 'invalid_email' =1 WHERE 'email' = ("string_value" in the middle of a blob in) TABLE 'emails' FIELD 'description' WHERE 'name' CONTAINS_STRING "failure"   or "Failure" or "FAILURE"
    Two examples of the text from FIELD ‘description’:

    ‘description’ Example 1:
    Code:
    Hi. This is the qmail-send program at clients5.estreet.com.
    I'm afraid I wasn't able to deliver your message to the following addresses.
    This is a permanent error; I've given up. Sorry it didn't work out.
    
    <jeb1561@aol.com>:
    205.188.158.121 does not like recipient.
    Remote host said: 550 MAILBOX NOT FOUND
    Giving up on 205.188.158.121.
    ‘description’ Example 2:
    Code:
    This is an automatically generated Delivery Status Notification.
    
    Delivery to the following recipients failed.
    
           rshields@hcpeck.com
    Two examples from FIELD ‘name’
    Code:
    ‘name’ Example 1: “Delivery Status Notification (Failure)”
    ‘name’ Example 2: “failure notice”

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    bit late for me to think about such things, but 'LIKE', the wildcard '%' and 'lower' (or 'upper') are going to be involved...

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    I had gotten to the LIKE, and % part, haven't come across lower or upper yet, but sounds like there's hope, and that I might even have succeeded in presenting my need in not too confusing a fashion????

  • #4
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Okay, a little progress here.

    This query returns the desired records without reference to the FIELD emails.name OR use of LIKE "%FAILURE%" (which also works but doesn't appear necessary)

    Code:
    SELECT * FROM emails WHERE emails.description LIKE "%@%"
    but this (for reasons that I'm hoping are obvious to you all) DOES NOT WORK

    Code:
    UPDATE contacts, emails
    WHERE contacts.email LIKE emails.description LIKE "%@%"
    SET contacts.invalid_email=1
    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE contacts.email LIKE emails.description LIKE "%@%"
    SET con...


    Trying to simplify I attempted:

    Code:
    SELECT * 
    FROM contacts
    WHERE contacts.email LIKE emails.description LIKE "%@%"
    which of course doesn't work either!
    Last edited by fuzzy1; 09-23-2006 at 05:27 PM.

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    swap the 'where' and 'set' bits around
    UPDATE table
    SET field='something'
    WHERE field='somethingelse'

  • #6
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by GJay View Post
    swap the 'where' and 'set' bits around
    UPDATE table
    SET field='something'
    WHERE field='somethingelse'
    Tried
    Code:
    UPDATE contacts, emails
    SET contacts.invalid_email=1
    WHERE contacts.email LIKE emails.description LIKE "%@%"
    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE "%@%"' at line 3

    ... and I'm thinking that the table.field LIKE table.feild LIKE "" part is where my failure resides? Also wondering about the fact that I'm not actually "UPDATING" the table 'emails' (though I'm hoping to delete the records at the end of this)???

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Your syntax is a little bit askew. If you want to test two columns for likeness you just need to do WHERE blah LIKE 'blah%' AND blah2 LIKE 'blah2%'.

  • #8
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Your syntax is a little bit askew. If you want to test two columns for likeness you just need to do WHERE blah LIKE 'blah%' AND blah2 LIKE 'blah2%'.
    Thanks, but you completely lost me there.
    backing away from the total queary let me attempt to clarify.

    First let’s try and pair this back to a somewhat simpler SELECT statement

    Say that I know there are 300 records in TABLE 'emails'
    AND that of those records there are 100 in which – somewhere in the FIELD ‘description’ -- is contained an email address (intended recipient of failed email delivery)

    Now, I also know that each of those 100 email addresses for which delivery has failed correspond to 1 of some 4000 records in the TABLE ‘contacts’.

    How might I return only the records in TABLE ‘contacts’ WHERE the value of 'contacts.email1' (emailAddress@some.com) is equal to or LIKE the email_string contained within the blob of text in Table/field 'emails.description' ???
    Code:
    Select * FROM contacts
    WHERE contacts.email1 LIKE %@% 
    AND emails.description LIKE %@%   
    ???
    Clearly this will NOT work as the value in contacts.email1 is LIKE %@% for ALL 4000+ records so -- clearly I'm confused as to your meaning.

    Thanks for the help.

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Now that I know what you want to do, I can maybe offer some help. The problem you have is, you don't know the email address value prior to going into the query. That makes it kinda tricky. Even if you did know the email address values, because the email address is not in its own column but inside another column along with other text, a join would not use indexes so the query would be really slow.

    Using '%@%' to isolate the email address just isn't going to work. A regular expression is probably the only way you're going to succeed in isolating the email address.

    Is there any way you can create a new column that contains just the email address? That would be a much better way of handling your data. If this is not possible. you'll probably have to dump your table into a PHP array, retrieve the email address via regex, then do another query looking for that email address using LIKE '%$emailArray[0]%'.

  • #10
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Whew! Now we're getting somewhere!

    Still -- quoting Al Pochino -- I'M IN THE DARK HERE!

    Columns I can create, and even given my utter cluelessness I can see how it would be relatively easy to update for all WHERE contacts.emal1=emails.newaddressColumn, but as I have no idea how to go about extracting the email addresses from the text in the existing coulmns, I'm diving into regular expressions now, hoping to have a clue on that count sometime soon.

    Thanks again!

    I'll be back.


  •  

    Posting Permissions

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