[sqlite] optimizing an update

[Sam Carleton]

I have a select statement that is calculating the subTotal, Tax, and grand
total:

UPDATE Invoice
   SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where
INVOICE_ID = @invoiceId),
       Tax = (select round( sum(PRICE * QTY) * ( @tax / 100) + .005, 2) from
INVOICE_ITEM where INVOICE_ID = @invoiceId),
       Total = (select round( sum(PRICE * QTY) * ( 1 + ( @tax / 100)) +
.005, 2) from INVOICE_ITEM where INVOICE_ID = @invoiceId),
       updatedby = @updatedby
 WHERE Invoice_Id = @invoiceId;

One thing I don't like is that each column contains the same basic inner
select.  Can I optimize the query such that the Sub_Total is used in
calculating the Tax and Total:

UPDATE Invoice
   SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where
INVOICE_ID = @invoiceId),
       Tax = round( Sub_Total * ( @tax / 100) + .005, 2)
       Total = (round( Sub_Total * ( 1 + ( @tax / 100)) + .005, 2),
       updatedby = @updatedby
 WHERE Invoice_Id = @invoiceId;

P.S.  I just added the Sub_Total column to the table and need to do a one
time calculation of ALL the Sub_Total columns, for some reason this is not
working:

UPDATE Invoice SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM as
i1 where i1.INVOICE_ID = INVOICE_ID),
updatedby = 'manual';
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users