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: 247180

  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

  ---

• 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: 2838

  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