UPDATE 1 TABLE FROM VALUES IN ANOTHER TABLE

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  July 17, 2009 at 12:21 pm

  #75504

  I have a table defined as follows:

  Participants

  Participant varchar(5)

  SpanStart int

  SpanEnd int

  OriginalStart int

  Example data is

  PARTICIPANT SPANSTART SPANEND ORIGINALSTART

  12345 20090101 0

  I need to update the Participant table OriginalStart column

  with the latest startdate from another table, membership,

  which is less than or equal to the spanstart in the participant table.

  Here is the Membership table ddl and sample data

  Member varchar(5)

  Startdate int

  Enddate int

  MemberNbr Startdate Enddate

  12345 20070101 20071231

  12345 20080201 20090131

  12345 20090301 0

  So in the case described above, I need to populate the

  participant table record originalstart with 20080201

  Any TSQL help?

• Loner

  SSC-Insane

  Points: 21279

  More actions

  July 17, 2009 at 1:27 pm

  #1026405

  I assume participant is the same as member according to the data that you provided.

  UPDATE p

  SET OriginalStart = m.Startdate

  FROM Participant p

  INNER JOIN Member m ON p.Participant = m.Member

  WHERE p.Spanstart between m.Startdate and m.enddate