UPDATE question

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  December 21, 2004 at 2:38 pm

  #61502

  Good Afternoon!

  I have two tables:

  tbl1 has voucher_number and payment_date

  tbl2 has payment_id, voucher_number and payment_date

  The payment_date field in tbl2 is currently empty.

  I need to UPDATE tbl2 payment_date with tb1 payment_date.  Seems simple but the challange is that each voucher_number can have more than one payment and tbl1 has no payment_id field.

  How can I get the first payment_date from tbl1 into the first payment_date in tbl2, the second into the second and so on, for a given voucher_number, if tbl1 only has voucher_number and payment_date?

   

  Thank you!

  jmatt

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  December 21, 2004 at 4:01 pm

  #534693

  The Payment_ID Column sounds like the Primary Key in tbl2. If you could Insert the Payment_ID into tbl1 based on tbl2 voucher_number, then updating tbl2 payment_date would be very simple.

  ---

  Kindest Regards,

• Jason Willis

  SSC Veteran

  Points: 225

  More actions

  December 21, 2004 at 5:29 pm

  #534699

  This may not be the best answer but I have a similar situation. I have a helpdesk table and a helpdesk history table. I have a trigger that inserts a row every time an insert or update happens. The only difference is that I use a sequence number on the history table. Maybe that helps, if you want I can post the trigger.

   

   

• Phil Parkin

  SSC Guru

  Points: 246979

  More actions

  December 21, 2004 at 6:38 pm

  #534703

  Agree with this. Alternatively, you could create a 'Payment Number' field (1, 2, ...) in both tables and then match on Voucher Number and Payment Number to perform the update.

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  December 22, 2004 at 9:49 am

  #534795

  Thanks all for your replys.

  I need to be more explicit ...

  This is a one-time update, tbl1 is imported into my SQL databse from another system and will be used to do a one-time update of tbl2.  In other words, there is no opportunity for a payment_id field in tbl1.

  I need the earlest date for a voucher in tbl1 to be placed into the lowest payment_id date field for that same voucher in tbl2.  Then the next earliest date in tbl1 for the voucher goes into the next payment_id date in tbl2 and so on ...

  Can this be done?

  Thanks!

  jmatt

   

• Donald Sawford

  SSCrazy

  Points: 2778

  More actions

  December 22, 2004 at 11:33 am

  #534806

  Is there a 1 to 1 relationship between voucher payments in tbl1 and tbl2? I think that it could be done with a cursor on tbl1 if there is no better way, but if there might be payments for a voucher in a table that have no corresponding record for that voucher to update or be updated in the other, it might affect the best means of doing it.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  December 22, 2004 at 11:58 am

  #534809

  This may work for you: 

   

  CREATE TABLE #tbl1( VoucherNum varchar(10),

    PaymentDate smalldatetime) 

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'ABC123', '12/31/2004'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'ABC123', '12/25/2003'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'ABC123', '01/01/2004'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'DEF123', '12/25/2004'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'DEF123', '12/25/2004'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'GHI123', '12/31/2003'

  INSERT INTO #tbl1( VoucherNum, PaymentDate)

  SELECT 'GHI123', '12/25/2004'

  CREATE TABLE #tbl2( PaymentID integer IDENTITY(1, 1),

    VoucherNum varchar(10),

    PaymentDate smalldatetime) 

  INSERT INTO #tbl2( VoucherNum)

  SELECT DISTINCT VoucherNum FROM #tbl1 

  UPDATE #tbl2 SET

   PaymentDate = a1.PaymentDate

  FROM #tbl2 a2

       INNER JOIN( SELECT a.VoucherNum, MAX( a.PaymentDate) AS PaymentDate

    FROM #tbl1 a

         INNER JOIN #tbl2 b ON( a.VoucherNum = b.VoucherNum)

    GROUP BY a.VoucherNum) a1 ON( a2.VoucherNum = a1.VoucherNum)

  SELECT * FROM #tbl2

  DROP TABLE #tbl1

  DROP TABLE #tbl2

  I wasn't born stupid - I had to study.

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  December 29, 2004 at 6:38 am

  #535237

  I apologize for the delay in responding.

  Thanks to all that responded!

  Farrell, I used a variation of your suggestion ... the identity field in the temp table was the key for me.

   

  jmatt