SEO

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, 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 "%@%"

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 <ferindo.middleton@gmail.com> 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
>
>

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 <drbuettner@gmail.com> 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 "%@%"
>
> 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 < ferindo.middleton@gmail.com> 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-

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 <ferindo.middleton@gmail.com> 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 < drbuettner@gmail.com> 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 "%@%"
> >
> > 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 < ferindo.middleton@gmail.com> 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-

You might want to check for an empty string ("") rather than null. From what
I can tell, HTML forms don't give you NULL values if you leave fields empty,
they return "". Unless your programs internally convert empty strings to
NULL, you won't find NULL in your table.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Ferindo Middleton [mailto:ferindo.middleton@gmail.com]
> Sent: Saturday, October 14, 2006 9:16 PM
> To: Dan Buettner
> Cc: mysql
> Subject: Re: help with update query
>
> 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 <drbuettner@gmail.com> 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 "%@%"
> >
> > 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 < ferindo.middleton@gmail.com> 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-
>

I agree. I should check for empty strings intead of nulls. The application
doesn't convert them to null and the default value when a user leaves the
field blank on the web page is to save it as an empty string. Thanks.

Ferindo

On 10/16/06, Jerry Schwartz <jschwartz@the-infoshop.com> wrote:
>
> You might want to check for an empty string ("") rather than null. From
> what
> I can tell, HTML forms don't give you NULL values if you leave fields
> empty,
> they return "". Unless your programs internally convert empty strings to
> NULL, you won't find NULL in your table.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
> > -----Original Message-----
> > From: Ferindo Middleton [mailto:ferindo.middleton@gmail.com]
> > Sent: Saturday, October 14, 2006 9:16 PM
> > To: Dan Buettner
> > Cc: mysql
> > Subject: Re: help with update query
> >
> > 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 <drbuettner@gmail.com> 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 "%@%"
> > >
> > > 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 < ferindo.middleton@gmail.com> 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-
> >
>
>
>
>


--
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-