...

View Full Version : difficult update involving multiple tables



fuzzy1
09-22-2006, 11:07 PM
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


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:

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:

This is an automatically generated Delivery Status Notification.

Delivery to the following recipients failed.

rshields@hcpeck.com
Two examples from FIELD ‘name’

‘name’ Example 1: “Delivery Status Notification (Failure)”
‘name’ Example 2: “failure notice”

GJay
09-22-2006, 11:58 PM
bit late for me to think about such things, but 'LIKE', the wildcard '%' and 'lower' (or 'upper') are going to be involved...

fuzzy1
09-23-2006, 12:15 AM
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????

fuzzy1
09-23-2006, 04:21 PM
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)


SELECT * FROM emails WHERE emails.description LIKE "%@%"

but this (for reasons that I'm hoping are obvious to you all) DOES NOT WORK


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:


SELECT *
FROM contacts
WHERE contacts.email LIKE emails.description LIKE "%@%"which of course doesn't work either!:p

GJay
09-23-2006, 04:23 PM
swap the 'where' and 'set' bits around
UPDATE table
SET field='something'
WHERE field='somethingelse'

fuzzy1
09-23-2006, 04:36 PM
swap the 'where' and 'set' bits around
UPDATE table
SET field='something'
WHERE field='somethingelse'Tried
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)???

Fumigator
09-23-2006, 05:34 PM
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%'.

fuzzy1
09-23-2006, 06:38 PM
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' ???
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.

Fumigator
09-23-2006, 07:31 PM
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]%'.

fuzzy1
09-23-2006, 08:29 PM
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.:D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum