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

Question

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
March 3, 2021 at 4:24 pm

#3852945
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
```

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 1

That is a nice and easy one:

 UPDATE [#partswithcompany]
 SET [companyId] = [PARTS].[companyId]
 FROM (SELECT [partNumber], MAX(companyID) OVER (PARTITION BY partnumber) AS companyID FROM [#parts]) AS PARTS
 WHERE [PARTS].[partNumber] = [#partswithcompany].[partNumber]

Basically, nested select (or could use a CTE) to get the max company ID per part number and you are good to go. Only exception is that your expected results for ldf351 don't match your data. Company ID should be 7010, not 8440 UNLESS that one has some special rule that it uses the Part ID instead of the Company ID?

Also, if you don't like the OVER (PARTITION BY partnumber) part of the code, you can remove that and put in a GROUP BY in the nested select instead. I just like windowing functions (and I probably overuse them).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

kaj SSCommitted Points: 1580 More actions · Answer 2

There are a few additional variations that will achieve the same. The resulting query plan will probably look slightly different too.

 update pwc
 set pwc.companyId = p.CompanyId
 from #partswithcompany pwc
 cross apply (
 select top(1) CompanyId
 from #parts 
 where PartNumber=pwc.partNumber
 order by CompanyId desc
 ) p

 update pwc
 set pwc.companyId = p.CompanyId
 from #partswithcompany pwc
 cross apply (
 select max(CompanyId) as CompanyId
 from #parts 
 where PartNumber=pwc.partNumber
 ) p

 update pwc
 set pwc.companyId = p.CompanyId
 from #partswithcompany pwc
 inner join (
 select PartNumber, max(CompanyId) as CompanyId
 from #parts 
 group by PartNumber
 ) p on p.PartNumber=pwc.partNumber

with p as (
 select PartNumber, max(CompanyId) as CompanyId
 from #parts 
 group by PartNumber
 )
 update pwc
 set pwc.companyId = p.CompanyId
 from #partswithcompany pwc
 inner join p on p.PartNumber=pwc.partNumber