Updating Parent table records with sum of chile records

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  October 19, 2005 at 9:00 am

  #89787

  Hi All.

  SQL Server 2000

  Can someone please help with the following: -

  Structure:

  tblCustomer

  CustomerID (int)

  PaymentCount (int)

   

  tblPayment

  CustomerID (int)

  PaymentID (int)

  PaymentDate (datetime)

  I want to update the customer (PaymentCount) with the total number of related payments in tblPayment.

  Can someone help me with the Update tsql.

  Thankyou

  CCB.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  October 19, 2005 at 9:20 am

  #598317

  DECLARE @tblCustomer TABLE( CustomerID integer, 

                                               PaymentCount integer)

  INSERT INTO @tblCustomer

  SELECT 1, NULL UNION ALL

  SELECT 2, NULL UNION ALL

  SELECT 3, NULL UNION ALL

  SELECT 4, NULL UNION ALL

  SELECT 5, NULL

  DECLARE @tblPayment TABLE( CustomerID integer, 

                                              PaymentID integer, 

                                              PaymentDate datetime)

  INSERT INTO @tblPayment

  SELECT 1, 1, '12/31/2002' UNION ALL

  SELECT 1, 2, '1/28/2003' UNION ALL

  SELECT 2, 1, '12/31/2002' UNION ALL

  SELECT 2, 2, '1/31/2003' UNION ALL

  SELECT 2, 3, '2/28/2003' UNION ALL

  SELECT 3, 1, '12/31/2002' UNION ALL

  SELECT 4, 1, '12/31/2002' UNION ALL

  SELECT 4, 2, '6/1/2003' UNION ALL

  SELECT 5, 1, '12/31/2002' UNION ALL

  SELECT 5, 2, '6/1/2003' UNION ALL

  SELECT 5, 3, '12/31/2003' UNION ALL

  SELECT 5, 4, '6/1/2004'

  UPDATE @tblCustomer SET

           PaymentCount = PC.PaymentCount

  FROM @tblCustomer Customer

     INNER JOIN( SELECT CustomerID, COUNT(*) AS PaymentCount

                         FROM @tblPayment

                         GROUP BY CustomerID) PC ON(  Customer.CustomerID = PC.CustomerID)

  SELECT * FROM @tblCustomer

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

• Victor Sokol

  Valued Member

  Points: 72

  More actions

  October 19, 2005 at 9:20 am

  #598316

  UPDATE tblCustomer

   SET PaymentCount = (SELECT Count(PaymentID) FROM tblPayment P WHERE P.CustomerID = C.CustomerID)

   FROM tblCustomer C

• Charlottecb

  Hall of Fame

  Points: 3932

  More actions

  October 19, 2005 at 9:30 am

  #598325

  Farrell, that worked a treat.

  Thanks

  CCB.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  October 19, 2005 at 9:36 am

  #598327

  Glad to help. 

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