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
