I have following SQL UPDATE query:
UPDATE Invoices
SET Price = (SELECT SUM(Price*ProductsAndServices.Amount) FROM InvoicesItems
LEFT JOIN ProductsAndServices ON ProductsAndServices.ProductsAndServicesID = InvoicesItems.ProductsAndServicesID
WHERE Invoices.InvoicesID = InvoicesItems.InvoicesID)
I have following TABLES | Columns (i am providing only the important-ones):
Invoices | (InvoicesID, Price)
InvoicesItems | (InvoicesItemsID,InvoicesID,ProductsAndServicesID,Amount)
ProductsAndServices | (ProductsAndServicesID, Price)
I want to create query that takes all the products from ProductsAndServices for invoice. Multiply it by amount (this column is for amount of this Product), And than update the final invoice price.
Can you please tell me, how to do it?
- If i removed "
* ProductsAndServices.Amount
" It said the error that is in title..." - When i leave there this Amount multiplying, it prompts for value of
ProductsAndServices.Amount
Thanks a lot