One more way:-)

2 remarks before the discussion about the way to update your table:

1)Try not to use column names that are also reserved words in SQL Server. The column name user is not a good name and it forces us to use square brackets in the code

2)In you example you had different customer names PESS and PESSY. In the requested solution you treated both customers as the same one. I treated it as a typo.

There are few ways to do what you want. The steps to do it are to identify the columns that should be updated and then update those columns in an update statement that uses from clause with join. I've used CTE that marks those records and give them a value of 1 in a column that is called RowNum. Then I run an update statement on the CTE. The code bellow shows it:

with MyCTE as (

select Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2,

row_number() over (partition by Location, ProdCode, Customer order by Date_Effective desc) AS RowNum

from Items_TEST)

update MyCTE

SET MyCTE.Item = Staging1.Item,

MyCTE.Date_Effective = Staging1.Date_Effective,

MyCTE.Source = Staging1.Source,

MyCTE.[User] = Staging1.[User],

MyCTE.Date_Exported = Staging1.Date_Exported,

MyCTE.Time_Exported = Staging1.Time_Exported,

MyCTE.Add1 = Staging1.Add1,

MyCTE.Add2 = Staging1.Add2

from Staging1 inner join MyCTE on Staging1.Customer = MyCTE.Customer

AND Staging1.Location = MyCTE.Location

AND Staging1.ProdCode = MyCTE.ProdCode

AND MyCTE.RowNum = 1

Adi