MS Access 2016 - SQL for Update Query that updates one column with the value of another column

LeighWardle
LeighWardle used Ask the Experts™
on
Hi Experts,

I would like to have an Update query that updates one column with the value of another column.
I'm don't have a clue how to do it.

Here are the details of my Select query:

My database is attached:  LOADS7.mdb.

Here's the Design View:

Design View
Here's the SQL:

SELECT TrafficLoadDistributionMetaData.ID, TrafficLoadDistributionMetaData.[ESA per HV], [TrafficLoadDistributionMetaData-ESA per HV].[ESA per HV]
FROM [TrafficLoadDistributionMetaData-ESA per HV] INNER JOIN TrafficLoadDistributionMetaData ON [TrafficLoadDistributionMetaData-ESA per HV].ID = TrafficLoadDistributionMetaData.ID;

Open in new window


Here are the results of the Select Query:

Output from Select Query
In summary, looking at the results above, I want the numbers in the right hand column to be copied to the blank column.

Regards,
Leigh
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
Hi Leigh,

I think you are after this:

UPDATE [TrafficLoadDistributionMetaData-ESA per HV]
INNER JOIN [TrafficLoadDistributionMetaData] ON [TrafficLoadDistributionMetaData-ESA per HV].ID = TrafficLoadDistributionMetaData.ID
SET
[TrafficLoadDistributionMetaData-ESA per HV].[ESA per HV] = TrafficLoadDistributionMetaData.[ESA per HV]


If the other way around you can use the one below:

UPDATE TrafficLoadDistributionMetaData
INNER JOIN [TrafficLoadDistributionMetaData-ESA per HV] ON [TrafficLoadDistributionMetaData-ESA per HV].ID = TrafficLoadDistributionMetaData.ID
SET
TrafficLoadDistributionMetaData.[ESA per HV] = [TrafficLoadDistributionMetaData-ESA per HV].[ESA per HV]

Author

Commented:
Thanks, Ares.

The 2nd version works for me.

Cheers,
Leigh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial