Good call on the WHERE email_address IS NULL thing. Also occurs to me you could do a SELECT DISTINCT instead of just a SELECT to eliminate duplicate update commands.
Glad this was useful. Dan On 10/14/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote:
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-
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
