SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

(No column name)
Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

declare @Product table  (ProductId int, ProductName varchar(100), SortValue int)
insert into @Product select 1157      , 'ABC'         , 7 
insert into @Product select 1156      , 'DEF'         , 3 
insert into @Product select 1155      , 'GHI'         , 4 
insert into @Product select 1154      , 'JKL'         , 2 
insert into @Product select 1153      , 'MNO'         , 1 
insert into @Product select 1152      , 'PQR'         , 5 
insert into @Product select 1151      , 'STU'         , 6

--select * from @Product

declare @LocationProduct table (fkLocationId int, fkProductId int, Sortvalue int)
insert into @LocationProduct select 19           , 1157        , 1
insert into @LocationProduct select 19           , 1156        , 2
insert into @LocationProduct select 19           , 1155        , 3
insert into @LocationProduct select 19           , 1154        , 4
insert into @LocationProduct select 19           , 1153        , 5
insert into @LocationProduct select 19           , 1152        , 6
insert into @LocationProduct select 19           , 1151        , 7
insert into @LocationProduct select 20           , 1155        , 3
insert into @LocationProduct select 20           , 1154        , 4
insert into @LocationProduct select 20           , 1153        , 5
insert into @LocationProduct select 20           , 1152        , 6
insert into @LocationProduct select 20           , 1151        , 7
insert into @LocationProduct select 21           , 1155        , 3
insert into @LocationProduct select 21           , 1154        , 4
insert into @LocationProduct select 21           , 1153        , 5
insert into @LocationProduct select 21           , 1152        , 6
insert into @LocationProduct select 21           , 1151        , 7

--select * from @LocationProduct

UPDATE        LP
SET           LP.Sortvalue = P.SortValue
FROM          @LocationProduct LP
INNER JOIN    @Product P
ON            LP.fkProductId = P.ProductId
WHERE fkLocationId IN (19,20)

SELECT * FROM @LocationProduct

fkLocationId	fkProductId	Sortvalue
19	1157	7
19	1156	3
19	1155	4
19	1154	2
19	1153	1
19	1152	5
19	1151	6
20	1155	4
20	1154	2
20	1153	1
20	1152	5
20	1151	6
21	1155	3
21	1154	4
21	1153	5
21	1152	6
21	1151	7