Update the first row only

• Joe Contreras

  Ten Centuries

  Points: 1341

  More actions

  January 4, 2006 at 2:07 pm

  #96252

  I'm trying to update the first row only for a given set of rows that are return.  I have a quantity amount from one table that I need to update only the first row in the second table.  For example

  In table one

  part no.   totalqty

  12           200

   

  In table two

  salesno.   part no.  current stock   totalqty     startavailable

  p1           12          10                 200           210

  2             12            2

  3             12            2

  What I need to do is update the totalavailable field with the 200 value only for the first record the other records do not get the value posted

  Thanks

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  January 4, 2006 at 2:49 pm

  #612729

  -- Heres one way

  create table #TableOne (partNo int identity, TotalQty int)

  Insert into #TableOne (TotalQty)

  select 10 union

  Select 500 union

  select 333

  Create table #TableTwo (SalesNo int identity, PartNo int, StartAvailable int)

  Insert into #TableTwo (PartNo)

  Select 1 union all

  select 2 union all

  select 1 union all

  select 2 union all

  select 3 union all

  select 1

  select * from #TableOne

  select * from #TableTwo

  Update A

  set StartAvailable = b.TotalQty

  From #TableTwo A

  Join #TableOne B on a.PartNo = b.PartNo

  Where a.SalesNo in (select min(SalesNo) from #TableTwo C

  Group By PartNo)

  Results

  1 1 10

  2 2 333

  3 1 NULL

  4 2 NULL

  5 3 500

  6 1 NULL

   

• DanielG-259128

  SSC Veteran

  Points: 291

  More actions

  January 5, 2006 at 1:00 am

  #612788

  Assuming your salesNo is unique and incremental.

  UPDATE A

  set A.totalavailable = C.Totalqty

  FROM

  TableTwo A,

  (

    Select Top 1 Salesno

    FROM TableTwo WITH (NOLOCK)

  Order By salesno Desc

  ) B,

  Table1 C

  WHERE

   A.Salesno = B.Salesno

  and A.partno=C.partno

  Subquery B will determine the exact record that gets updated.

• Joe Contreras

  Ten Centuries

  Points: 1341

  More actions

  January 5, 2006 at 7:43 am

  #612889

  Thanks all for the help here is my final query that did the trick:

  UPDATE a SET totalavailable = b.projstocknumb + b.stocksupply

  FROM procurement_wo_supply a INNER JOIN procurement_initialprojectstock b

   ON a.itemno = b.stockitemno

  WHERE a.salesorder IN (SELECT MIN(salesorder) FROM procurement_wo_supply c WHERE linestatus = 'FP' GROUP BY itemno)

  This update the first sales order with the total supply available. 

  Thanks Again,

  Joe

• Pradeep Chakraborty

  Valued Member

  Points: 61

  More actions

  January 5, 2006 at 1:42 pm

  #613035

  Try row Count

  Set RowCount =1

  Update .....