Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    25

    Unanswered: Baffled by reasonably Simple Update

    I have created a simple database with a 'products', 'invoices', and 'items'. The updates I'm trying to run are:

    1) to update the invoices.total according to the sum of the items.lineTotal related to that invoice; and

    2) to update the products.qtySold according to sum of the items.qty for that productID.

    I have included the SQL_Dump.

    If anyone can shed any light on this, I'd be most thankful.

    All the best,

    mvreade
    Attached Files Attached Files

  2. #2
    Join Date
    May 2007
    Posts
    8
    Hi,

    1) to update the invoices.total according to the sum of the items.lineTotal related to that invoice;

    Update invoices set total=(select SUM(lineCost) from items where
    invoices.invoiceID=items.invoiceID)


    2) to update the products.qtySold according to sum of the items.qty for that productID.

    Update products set qtySold=(select SUM(qty) from items where
    products.productID=items.productID)


    Above queries may solve your problem.

    Thanks,

  3. #3
    Join Date
    Sep 2006
    Posts
    25
    Beautiful, thanks.

    All the best,

    mvreade

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •