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

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.