Search code examples
sqlms-accessjoinsql-updatesubquery

Updating main table to replace 'Email' field with ID of the email located in another 'Email Addresses' Table

I have two tables, The main table is a list of customers and their contact information. What I am trying to do is to separate the 'Email' field into a separate table so that I can relate the two because some customers use the same email address. I have created another table called 'Email Addresses' containing only unique emails and I want to now update the main table with the ID's of the emails from the 'Email Addresses' table but am having trouble with the SQL query.

I have researched and found a solution but it has not worked for me. It comes up with a 'Missing Operator' error.

UPDATE [Main Table]
SET [Main Table].Email = [Email Addresses].ID
FROM [Main Table]
INNER JOIN [Email Addresses] ON [Main Table].Email = [Email Addresses].Email;

What makes more sense is this though I know it will not work:

UPDATE [Main Table]
SET [Main Table].Email = [Email Addresses].ID
WHERE [Main Table].Email = [Email Addresses].Email;

Only new to SQL and still fumbling around in MS Access

Solution

  • Try these steps:

    • Change field [Email Addresses].[Id] to an AutoNumber (numeric or GUID)
    • Add the field [Main Table].[EmailId] of data type numeric or GUID matching the Id above
    • Run this query:
    UPDATE 
    [Main Table] 
INNER JOIN 
    [Email Addresses] 
    ON [Main Table].Email = [Email Addresses].Email 
SET 
    [Main Table].EmailId = [Email Addresses].Id;
    • Remove field [Main Table].Email