ahmed elbarbary

ahmed elbarbary

  • 994
  • 1.3k
  • 76.9k

How to update table partwithcompany column name company id by companyi

Mar 3 2021 4:26 PM
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

  1. create table #partswithcompany  
  2.  (  
  3.  partNumber nvarchar(50),  
  4.  companyId int  
  5.  )  
  6.  insert into #partswithcompany(partNumber,companyId)  
  7.  values  
  8.  ('A74351',null),  
  9.  ('bmy351',null),  
  10.  ('ldf351',null)  
  11.       
  12.  create table #parts  
  13.  (  
  14.  PartId  int,  
  15.  CompanyId int,  
  16.  partNumber nvarchar(50)  
  17.  )  
  18.  insert into #parts(PartId,CompanyId,partNumber)  
  19.  values  
  20.  (2220,5000,'A74351'),  
  21.  (2290,5002,'A74351'),  
  22.  (3500,5003,'A74351'),  
  23.  (4000,5050,'bmy351'),  
  24.  (4200,5070,'bmy351'),  
  25.  (8230,7002,'ldf351'),  
  26.  (8440,7010,'ldf351')  
  27.   
  28.   
  29. Expected result  
  30.   
  31.  partNumber    companyId  
  32.  A74351    5003  
  33.  bmy351    5070  
  34.  ldf351    8440  

Answers (2)