Ms-access – Updating one table from another table where non-primary-key field is similar string

ms accessupdate

I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original).

There are two tables, one with data input errors, and a second that is a copy of those errors, but fixed, if that makes sense. However the second table only contains 3 out of 14-ish fields, and the primary key is not among them.

I would like to update the first table with the information from the second, searching for records to update based on similarity between the Title field in both. The second has had some typos fixed throughout as well…

I know some SQL but not much.

Best Answer

Without the primary key, fixing the data with an update statement is likely to get very messy. You're most likely better off getting a fresh export of the original data with the PK included, run the process/es that you did to clean up the data, then run an update statement. And then put constraints on the table to prevent the errors from occurring again.

The update statement should be fairly simple though. I don't know the full syntax for Access, but here's the general SQL statement I'd use on SQL Server:

update TableA
set FieldA = source.FieldA
from TableA destination
inner join TableB source
on destination.PK_ID = source.PK_ID
Related Question