SQL Query (lookup)
Hi Guys,
I need a SQL query that'd be able to do a lookup in a secondary table (such as VLookup in Excel), but I'd like to store the value in the primary table.
For example:
I have a Primary "members" table (name, surname, ID number and card ID)
* Card ID column is blank for all members
From the Secondary table (ID number, Card ID), I'd like to run a query on ID number (from primary table) to lookup the Card ID, and store the corresponding Card ID in the primary "members" table.
I need a SQL query that'd be able to do a lookup in a secondary table (such as VLookup in Excel), but I'd like to store the value in the primary table.
For example:
I have a Primary "members" table (name, surname, ID number and card ID)
* Card ID column is blank for all members
From the Secondary table (ID number, Card ID), I'd like to run a query on ID number (from primary table) to lookup the Card ID, and store the corresponding Card ID in the primary "members" table.
Putting it differently, you want to update [members].[Card ID] field per [Secondary table].[Card ID]
where [members].[ID number]= [Secondary table].[ID number]
where [members].[ID number]= [Secondary table].[ID number]
select t.name, surname, ID number, l.CardID
from mytable t inner join lookupTable l on t.ID=l.IDbut why you have 2nd table?
why your CardID is blank?
looks like a bad design to me
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if so, then
Update [members] SET m.[Card ID] = s.[Card ID]
From [members] m inner join [Secondary table] s on m.[ID number]= s.[ID number]ASKER
Thanks for the help!
It is a database designed a very long time ago, but we have to update some values in the primary table, from a secondary table lookup.
Tables are a bit more complex as in the example, but I've used this simple layout in my example, as this is in theory what we need to accomplish.
It is a database designed a very long time ago, but we have to update some values in the primary table, from a secondary table lookup.
Tables are a bit more complex as in the example, but I've used this simple layout in my example, as this is in theory what we need to accomplish.
for your database design:
it depends on whether a Member can have a maximum of 1 or more cards.
if a Member only can have a maximum of 1 card (one to one relationship), then you no need to normalise to have the secondary table, and hence the Card ID should be populated directly into table: Member.
if a Member can have multiple cards (one to many relationship), then you need to normalise to have the secondary table, we usually use the JOIN clause to link these 2 tables with the joined key (Card ID). Pls note for this approach, we NO need to store the Card IDs in Table: Member as it's the way suggested for table normalisation (that's to save the storage space and maintain data integrity)
Primary "members" table (name, surname, ID number and card ID)
* Card ID column is blank for all members
From the Secondary table (ID number, Card ID)
it depends on whether a Member can have a maximum of 1 or more cards.
if a Member only can have a maximum of 1 card (one to one relationship), then you no need to normalise to have the secondary table, and hence the Card ID should be populated directly into table: Member.
if a Member can have multiple cards (one to many relationship), then you need to normalise to have the secondary table, we usually use the JOIN clause to link these 2 tables with the joined key (Card ID). Pls note for this approach, we NO need to store the Card IDs in Table: Member as it's the way suggested for table normalisation (that's to save the storage space and maintain data integrity)
ASKER
Thank you, I don't need to join the two tables at this time.
We just needed the Card ID's filled in the primary table. We have a couple of joins (relationships) on the primary table, but this is not one that will help us.
I ran the query as per Huseyin's suggestion, and in the process of checking the results.
We just needed the Card ID's filled in the primary table. We have a couple of joins (relationships) on the primary table, but this is not one that will help us.
I ran the query as per Huseyin's suggestion, and in the process of checking the results.
Open in new window