question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ·

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

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




sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

I think there is a mistake in the output you posted since 8440 is the PartId for the PartNumber ldf351. The CompanyId should be 7010. Try this.

 ;WITH CTE_Max_PartId_per_PartNumber AS (
     SELECT partNumber, MAX(PartId) AS PartId
     FROM #parts
     GROUP BY partNumber
 ),
 CTE_Max_CompanyId_PartNumber AS (
     SELECT DISTINCT p.partNumber, p.CompanyId
     FROM #parts AS p
     INNER JOIN CTE_Max_PartId_per_PartNumber AS m ON m.partNumber = p.partNumber AND m.PartId = p.PartId
 )
    
 UPDATE pc
 SET pc.companyId = mcp.CompanyId
 FROM #partswithcompany AS pc
 INNER JOIN CTE_Max_CompanyId_PartNumber AS mcp ON mcp.partNumber = pc.partNumber;

73887-image.png



image.png (3.3 KiB)
· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think there is a mistake in the output you posted since 8440 is the PartId for the PartNumber ldf351. The CompanyId should be 7010.

I concur.



0 Votes 0 ·
ahmedsalah-1628 avatar image ahmedsalah-1628 YitzhakKhabinsky-0887 ·

yes you are correct
sorry

0 Votes 0 ·

@ahmedsalah-1628,

So you picked a more complicated solution with two CTEs instead of just one CTE as Answer?

0 Votes 0 ·