UPDATE TABLE FROM ANOTHER

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  October 20, 2005 at 2:25 pm

  #166005

  I have a tableA to update which includes a membership startdate and enddate column from a history tableB which also has a startdate and enddate.

  TableA startdate has to be the Min(startdate) for the member from TableB

  and TableA enddate has to be the LAST enddate in table B for the member which could be a default value of 0 (if the member has not terminated yet)

  TableA already has the MemberNbr inserted.

  Desired result for TableA

  TableA

  MemberNbr        StartDate          EndDate

  12345               20020101             0

  TableB

  Membernbr         Startdate          EndDate

  12345               20020101          20021231

  12345               20050101               0

   

  Any help with SQL to do the update of TableA?

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 20, 2005 at 3:02 pm

  #598943

  Update a set StartDate = IsNull(SD,0), EndDate = IsNull(ED,0)

  From TABLE_A a

         Left join  ( Select Membernbr, Min(StartDate) SD, Max(Endate) ED

               From TABLE_B

              GROUP BY Membernbr ) b  on a.Membernbr = b.Membernbr

   

  
  * Noel

• Paul Petre

  Newbie

  Points: 1

  More actions

  November 4, 2005 at 4:56 am

  #602305

  thx man .. fast solution , slow forum

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  November 4, 2005 at 9:23 am

  #602376

  noeld,

  your solution will not return zero if there is an end date of zero

  this should do it for the subquery

  Select Membernbr, Min(StartDate) as [SD],

  CASE WHEN Min(EndDate)=0 THEN 0 ELSE Max(EndDate) END as [ED]

  From TableB

  GROUP BY Membernbr

  Far away is close at hand in the images of elsewhere.
  Anon.