Thanks Dan. This does help. This a pretty straight-forward idea. I could even save the results of this query to a text file and possibly review it a little before running it so I don't acidentally do anything funky and I could see the impact this would have on the data before applying it. I think maybe I'll even add a "WHERE email_address IS NULL" line within the UPDATE concatenation so I don't overwrite any records that already have an email_address. I'll try this. Thanks alot!
Ferindo On 10/14/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
Ferindo, I had a similar task recently, and the problem you'll run into is that you can't select from and update the same table at once. What I ended up doing was doing a SELECT to build the update queries for me. Something like this: SELECT CONCAT( "UPDATE bowler_score SET email_address = '", email_address, "' ", "WHERE firstname = '", firstname, "' ", "AND middlename = '", middlename, "' ", "AND lastname = '", lastname, "' ", "AND race = '", race, "' ", "AND religion = '", religion, "'; " ) FROM bowler_score WHERE email_address LIKE "[EMAIL PROTECTED]" This finds all the entries where there appears to be a valid email address (contains @), and updates all the other records for that individual. Note this is not very efficient, since a LOT of update queries will be generated, and also that if one person has more than one email address (a typo perhaps) you will lose all but one address for them. But it should work, and it's pretty easy. HTH, Dan On 10/13/06, Ferindo Middleton < [EMAIL PROTECTED]> wrote: > I have a table, bowler_score_records, with the following columns: id, > firstname, middlename, lastname, race, religion, email_address, > bowling_score, gamedate > > As records get entered to this table, sometimes the users forget to input > the email_address but the users always capture the full name, race, and > religion. Assuming that no two individuals (bowlers) would happen to have > the same name, race, and religion. > > I need to write a query to update the email_address for all the records > where the users forgot to input it based on the idea that records carrying > the same full name, race, and religion are in fact the same person, hence > the same email_address. > > Based on the schema described above, how would you write it? > > -- > Ferindo > >
-- Ferindo Middleton Web Application Developer/Database Administrator/IT Infrastructure and Integration Management Specialist/Perception Augmentation and Control Supplementation Research Specialist for AI Wetware-to-Software Interface and Design -Sleekcollar-
