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

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,

Beautiful, thanks.

All the best,

mvreade