Append or Update? I can't figure it out

[NickD]

I have a table of of data that contains the fields 'Email' address and 'Alternate Email' addresses that are imported into a table. I've then created a duplicated table of the original data. What i'm trying to do, is update or append the emails from the 'Alternate Email' field to the 'Email' field whereby there is a matching criteria in the 'Alternate Email' field Like "*John Doe*".



For example, If record 250 in the 'Alternate Email' address field contained NickD@johndoe.co.uk, i want it to overwrite whats in the 'Email' field. If the 'Alternate Email' field is blank or doesn't contain 'John Doe', i want the update to skip to the next record.

The example Query as an update Query, i get the 40 records i would expect to see from the 'Alternate Email' field, but they won't update in the correct 'Email' field.

Any assistance on what i'm doing wrong wold be greatly appreciated....Do i need more Criteria rules or is my table selection incorrect as i've tried this multiple ways.


UPDATE UKG_People_Data_Feed_tbl_BAK INNER JOIN UKG_People_Data_Feed_tbl ON UKG_People_Data_Feed_tbl_BAK.[Employee Number] = UKG_People_Data_Feed_tbl.[Employee Number] SET UKG_People_Data_Feed_tbl.[Employee Number] = [UKG_People_Data_Feed_tbl_BAK].[Employee Number], UKG_People_Data_Feed_tbl.[First Name] = [UKG_People_Data_Feed_tbl_BAK].[First Name], UKG_People_Data_Feed_tbl.[Last Name] = [UKG_People_Data_Feed_tbl_BAK].[Last Name], UKG_People_Data_Feed_tbl.Username = [UKG_People_Data_Feed_tbl_BAK].[Username], UKG_People_Data_Feed_tbl.Country = [UKG_People_Data_Feed_tbl_BAK].[Country], UKG_People_Data_Feed_tbl.[Alternate Email] = [UKG_People_Data_Feed_tbl_BAK].[Email]
WHERE (((UKG_People_Data_Feed_tbl.[Alternate Email]) Like "*johndoe*"));

[Welshgasman]

Break it to down into small steps.
First. you want an Update query.
Get the correct data with a Select query.
Then in the design view change it to an update query and set the required fields.

However, moving data like that is not the norm for databases, unless you have made a huge mistake.

[jojowhite]

your current SQL is updating from the BAK table?

UPDATE UKG_People_Data_Feed_tbl_BAK 
    INNER JOIN UKG_People_Data_Feed_tbl 
        ON UKG_People_Data_Feed_tbl_BAK.[Employee Number] = UKG_People_Data_Feed_tbl.[Employee Number] 
SET UKG_People_Data_Feed_tbl.[Employee Number] = [UKG_People_Data_Feed_tbl_BAK].[Employee Number], 
UKG_People_Data_Feed_tbl.[First Name] = [UKG_People_Data_Feed_tbl_BAK].[First Name], 
UKG_People_Data_Feed_tbl.[Last Name] = [UKG_People_Data_Feed_tbl_BAK].[Last Name], 
UKG_People_Data_Feed_tbl.Username = [UKG_People_Data_Feed_tbl_BAK].[Username], 
UKG_People_Data_Feed_tbl.Country = [UKG_People_Data_Feed_tbl_BAK].[Country], 
UKG_People_Data_Feed_tbl.[Alternate Email] = IIF(NZ(UKG_People_Data_Feed_tbl.[Alternate Email], "@~") LIKE "*johndoe*", [UKG_People_Data_Feed_tbl_BAK].[Email], UKG_People_Data_Feed_tbl.[Alternate Email])

[NickD]

Hi Both, thanks for the swift response.

I probably should have explained better, so apologies for that.

In the current query, the 'Email' field where the value criteria "*johndoe*" is not present in the record field needs to be updated with the information in the 'Alternate Email' field.

I need the 'Alternate Email' field where the value criteria "*johndoe*" is updating the 'Email' field like 'Employee Number' 500009.

If the 'Alternate Email' doesn't contain the value criteria "*johndoe*", the update query skips to the next record containing "*johndoe*" to update.

As per the example, i'm trying to update row 500007 to match row 500009 (I know this example would create a duplicate, but i can't share examples of the original data)

Like i say, i can view the 40 records i need to move from 'Alternative Email' to 'Email', but can't get the query to update the correct field.

I hope the example above helps.

[NickD]

Break it to down into small steps.
First. you want an Update query.
Get the correct data with a Select query.
Then in the design view change it to an update query and set the required fields.

However, moving data like that is not the norm for databases, unless you have made a huge mistake.

Hi Both, thanks for the swift response.

I probably should have explained better, so apologies for that.

In the current query, the 'Email' field where the value criteria "*johndoe*" is not present in the record field needs to be updated with the information in the 'Alternate Email' field.

I need the 'Alternate Email' field where the value criteria "*johndoe*" is updating the 'Email' field like 'Employee Number' 500009.

If the 'Alternate Email' doesn't contain the value criteria "*johndoe*", the update query skips to the next record containing "*johndoe*" to update.

As per the example, i'm trying to update row 500007 to match row 500009 (I know this example would create a duplicate, but i can't share examples of the original data)

Like i say, i can view the 40 records i need to move from 'Alternative Email' to 'Email', but can't get the query to update the correct field.

I hope the example above helps.