Selecting data to update a row

• Larry Blanton-363707

  Newbie

  Points: 2

  More actions

  September 1, 2006 at 5:07 pm

  #172347

  I am having trouble getting this line to update the table.  It is working with data in a temp table and a table variable.  The table I am updating has 3 records for each resident.  The rows appear in both the table and the table variable. I am trying to update with data from the table variable that corresponds to data from one of the three records based on the month number in the date.  The stored proc has many other lines but but I am only showing the problem line.

  Thanks in advance for any help.  

  update tblResidentWeightsTemp set FirstWeight = (select RWeight from @WeightReportRaw as W where ResidentID = W.ResidentID and month(W.RWeightDate) = @intMonth3 and year(W.RWeightDate) = 2006)

• September 4, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 5, 2006 at 1:59 pm

  #658639

  Your SQL will perfectly works,if the data contains different months i.e, your subquery should not return more than 1 value

  try to identify the rows uniquely

   

• Larry Blanton-363707

  Newbie

  Points: 2

  More actions

  September 5, 2006 at 4:25 pm

  #658664

  Thanks for the input, I had to put another step in which built a unique record as follows.

  --Declare a temp table to hold the records for the first weight, the one to calculate percentage from

  de

  clare @FirstWeightTemp table

  (

  ResidentID

  int,

  FirstWeight

  float

  )

  --Put the data into the temp table and then update the temp table for the report, if i had any sense i could do this with less GOO

  insert into

  @FirstWeightTemp

  select

  ResidentID, FirstWeight from tblResidentWeightsTemp where FirstWeight > 0

  update

  tblResidentWeightsTemp set FirstWeight = (select FirstWeight from @FirstWeightTemp as R where tblResidentWeightsTemp.ResidentID = R.ResidentID)