Updating Location in inventory db

[rmcel]

I have an inventory database of approx 500 items. Items are held by stock # but also a dept 4 digit number that is barcoded on a sticker placed on the item. Our db has"last Location" as a column. Every 2 years we scan our inventory and import a file that has dept number and new location. I use the duplicate function to see what we have missed but once everything is found I would like to update the location column with the new location data. I have put all of the new scanned data under the db in the same columns to use the duplicate function. Can someone show me how to do this besides manually going through the whole db? I do have a separate db of all of the scanned files as well if that is an easier way to do it. Thanks in advance!

 

[rgd1101]

the table don't have item# as index?
Both new and old record in the same table, do they have maint date or create date?

 

[rmcel]

the table don't have item# as index?
Both new and old record in the same table, do they have maint date or create date?

The columns used are titled PHYSICS # and Location. There is a lot more columns for other info but I am just interested in those 2 columns. The scanned file (after manipulating data) is just PHYSICS # and then the column Location beside it. I need to compare the PHYSICS # columns and when there is a match, take the LOCATION info from the scanned file (it is just a 4 digit room #) and overwrite the Location from the original db. The 2 columns I am working with are beside each other. The Loc from the right needs to overwrite the Loc on the left if the Physics # numbers are the same

Physics Loc Physics Loc
1234 1234 1234 4321
2345 2345 2345 5432
3456 3456 3456 6543
4567 4567 4567 7654
5678 5678 5678 8765
6789 6789 6789 9876
7890 7890

 

[rgd1101]

a sql statement could do that, something like

update tablename 
set column1  = column2
where substring column1 = substring column2