I am try to update the Gender field for all females of a database with
about 15,000 records. So how I started was by searching baby girl
names on the web and manipulated some of there lists to create my own
table of female first names (HSNames). I can then use this table to
update the Gender field to 'F' (female) in the master table. This
process works great using the following query...

Update HSContacts
Set Gender = 'F'
Where First_Name IN
(Select FirstName
>From HSNames
Where Sex = 'F')

HSContacts is the master table with fields of Contact_UNO, First_Name,
Last_Name, Full_Name, Gender, etc..). HSNames contains fields named
UniqueID, FirstName, Sex.

Here is where the problem comes in... When we imported the field data
into the database many of the first name fields in the master table
also contained the middle initial (ex. Mary A.). How can I change my
query to include these records? Can I take use of the SQL LIKE command
with a subquery? Every way I look at it I get an error in SQL Server
2000 saying I cannot have a subquery spitting out multiple values with
this expression.

Any help would be appreciated. Thx.

(coryjflynn@hotmail.com) writes:
> Update HSContacts
> Set Gender = 'F'
> Where First_Name IN
> (Select FirstName
> From HSNames
> Where Sex = 'F')
>
> HSContacts is the master table with fields of Contact_UNO, First_Name,
> Last_Name, Full_Name, Gender, etc..). HSNames contains fields named
> UniqueID, FirstName, Sex.
>
> Here is where the problem comes in... When we imported the field data
> into the database many of the first name fields in the master table
> also contained the middle initial (ex. Mary A.). How can I change my
> query to include these records? Can I take use of the SQL LIKE command
> with a subquery?

Yes, but you neeed to switch to EXISTS:

Update HSContacts
Set Gender = 'F'
FROM HSConcatcs c
Where EXISTS (Select *
From HSNames n
Where n.Sex = 'F'
AND c.FirstName LIKE n.FirstName + '%')



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Of-course! I feel like an idiot. I can't believe I couldn't come up
with that. Thanks for getting me through my brain lapse...


Erland Sommarskog wrote:
> (coryjflynn@hotmail.com) writes:
> > Update HSContacts
> > Set Gender = 'F'
> > Where First_Name IN
> > (Select FirstName
> > From HSNames
> > Where Sex = 'F')
> >
> > HSContacts is the master table with fields of Contact_UNO,
First_Name,
> > Last_Name, Full_Name, Gender, etc..). HSNames contains fields
named
> > UniqueID, FirstName, Sex.
> >
> > Here is where the problem comes in... When we imported the field
data
> > into the database many of the first name fields in the master table
> > also contained the middle initial (ex. Mary A.). How can I change
my
> > query to include these records? Can I take use of the SQL LIKE
command
> > with a subquery?
>
> Yes, but you neeed to switch to EXISTS:
>
> Update HSContacts
> Set Gender = 'F'
> FROM HSConcatcs c
> Where EXISTS (Select *
> From HSNames n
> Where n.Sex = 'F'
> AND c.FirstName LIKE n.FirstName + '%')
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp