UPDATE Statement

• tsc1

  SSC-Addicted

  Points: 493

  More actions

  March 4, 2004 at 7:36 am

  #161228

  Hello,

  I have 2 tables

  TABLE 1

  • id
  • name
  • department
  • startdate
  • enddate
  • ruleinapplication

  TABLE 2

  • id2
  • name
  • month
  • department
  • other data

  I'd like to update DEPARTMENT for each month in TABLE2 with the value of the department in TABLE 1

  Thanks

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 4, 2004 at 7:53 am

  #497290

  can you say what happens when:

  1. a department on table1 has startdate and endate difference of more than 1 month

  2. when the year rolls over?

  3. if more than 1 department have the same month number in table1

   

  
  * Noel

• tsc1

  SSC-Addicted

  Points: 493

  More actions

  March 4, 2004 at 8:15 am

  #497312

  1 & 2  : month is a number YYYYMM (unique identifier= a period).

  startdate is a period

  3 : I always have enddate of a record = start of a record for a same name. and the dapartment may be the same or a different.

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 4, 2004 at 8:29 am

  #497317

  UPDATE Table2 SET Department = Table1.Department

  FROM TABLE1

  Where Table2.month =Table1.StartDate

  

  without some examples of the data this is what I can suggest.

   

  
  * Noel