How to update table partwithcompany column name company id by companyi

Question

How to update table partwithcompany column name company id by companyid for max partid ?

so i need to make

update partwithcompany set companyid=companyid for max partid

on table parts where partnumber from table parts equal partnumber from table

part withcompany

as example

I have partnumber A74351 on table partswithcompany

this part exist on table parts multiple time

so i will get company id from max partid where partnumber=partnumber

that meaning max partid on table parts for partnumber A74351 =3500

then i will get company id from partid 3500 that will be 5003

and update companyid column on table partwithcompany with value 5003

create table #partswithcompany
(
partNumber nvarchar(50),
companyId int
)
insert into #partswithcompany(partNumber,companyId)
values
('A74351',null),
('bmy351',null),
('ldf351',null)
create table #parts
(
PartId int,
CompanyId int,
partNumber nvarchar(50)
)
insert into #parts(PartId,CompanyId,partNumber)
values
(2220,5000,'A74351'),
(2290,5002,'A74351'),
(3500,5003,'A74351'),
(4000,5050,'bmy351'),
(4200,5070,'bmy351'),
(8230,7002,'ldf351'),
(8440,7010,'ldf351')
Expected result
partNumber companyId
A74351 5003
bmy351 5070
ldf351 8440

Amira Bedhiafi · Answer

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY partNumber ORDER BY PartId DESC ) AS rn FROM parts ) UPDATE partswithcompany SET companyId=CTE.CompanyId FROM partswithcompany as p INNER JOIN CTE ON p.PartNumber=CTE.partNumber WHERE CTE.rn=1; DBFiddle : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ce8385bee44448d152289c23dbcc2e71

Kiran Mohanty · Answer

Hi Try this. update #partswithcompany p set p.companyId= p1.CompanyId inner join #parts p1 on p.partNumber = p1.partNumber