SQL Query based on another table in the database?

Question

I have a table in my database that has two columns. The first column has some names of individuals and the second column has some group names. I have another table with over 500,000 records. What I want to do is to do a lookup of sorts on the two column table and wherever the individual name comes up in the large table, to return the group name. I am able to change the name with this query:

UPDATE CommonNames
SET Customer = 'Group A'
WHERE Customer = 'Bill Smith.'
OR Customer = 'John Smith'
OR Customer = 'Aaron Smith'

But then I will have to constantly update my query when the two column table changes (frequently). Is there a query I could use that does a lookup on the two column table and based on what's there changes the large table? Your help is greatly appreciated.

Chris

Unfortunately I can not add fields to either table. Also, my smith example was a poor one in the accounts being grouped will not necesarily have any common characteristics. The large table is a customer list. The small table is all of the major customers that have different businesses. So, if for tax reasons, person X decides to open Person X, LLC and that shows up in next month's data, I want to be able to get that grouped with the rest of Person X's accounts. So the company names will never change groups, some may go away from time to time, but there are new company names being added constantly.

Thank you everyone for your help, but I just got it working. The query that I used was:
Update LargeTable
Set Customer = SmallTable.Groups
From LargeTable, SmallTable
Where LargeTable.customer = SmallTable.customer

Again, thank you everyone for your help

Answer 1

First of all code your update as
UPDATE CommonNames
SET Customer = 'Group A'
WHERE Customer LIKE '% Smith.'

There is a space after the %. As long as it is only about surname then it will work.

Second. Any where in a select where a single value is required, this can be replaced by a select of a single value.
If multiple values are returned by a select then it can be put in an IN clause.

SELECT a.CustomerName
FROM Customer a
Where CustomerName = (SELECT
b.data
FROM CommonNames b
WHERE b.thing = b.thingb)

or

SELECT a.CustomerName
FROM Customer a
Where CustomerName in (SELECT
b.data
FROM CommonNames b
WHERE b.thing = b.thingb)

Answer 2

I think I can figure something out, but you need to tell more things about your data setup.

- How are these two tables related? (what roles do they play)
- Why does the two-column table (with the group name customer name) change so frequently? Is it because the customers change groups often, or just from adding customers?

It's sounding like you might be better off to just add the group field to the larger table. Then you end up with a lot of initial work to get the group column populated correctly, but depending on what criteria you use to place customers into groups, you might be able to use update queries to get most of the job done.

If that doesn't solve the problem, post back with more details and I'll add more to my answer. I hope that's at least a start on helping you out, though!

Answer 3

select
st.customername,
st.groupname
from smalltable st
inner join big table bt on bt.customername = st.customername

You reall need to have a customerID field as you're going to have huge data integrity problems when you have customers with the same name. Also doing joins on numerical fields is more efficiant and fasted than linking on text fields.