SQL Using LIKE Command with Subquery

[coryjflynn]

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.

[Erland Sommarskog]

(co********@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, es****@sommarskog.se

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

[coryjflynn]

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:

(co********@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, es****@sommarskog.se

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