QUESTION 1
I have to update some fields of a table of access according to the value of parameter. If this paramter is "true" I need to update.
Table
idInvoice
price
percentageTax1
tax1
percentageTax2
tax2
total
Example values:
idinvoice: 12300
price: 100 €
percentageTax1: 10 %
tax1= 10€
percentageTax2: 5 %
tax2: 5€
total: 115 € (result: 100€+10€+5€)
Ok. If the parameter on that I have commented before is "true" I must update the percentages and then update the total. I need to replace the "old" percentages by new percent.
Ok I can do it in 3 queries:
update invocies set percentageTax1=20,tax1=price *(percentageTax1/100) where idInvoice=@number and percentageTax1=10
update invocies set percentageTax2=7,tax2=price *(percentageTax2/100) where idInvoice=@number and percentageTax2=5
update invocies set total=price+tax1+tax2 where idInvoice=@number
. But my question is:
is there any an alternative to do this in 1 query?
QUESTION 2
Other question about update.
If I have 2 linked tables
EXAMPLE TABLE INVOICE
idInvoice
total
EXAMPLE TABLE DETAIL
idInovice
Line
price
The "total" field is the total sum of the field "price" of the 开发者_JS百科table "detail"
how it would be the "query" to update "total" field?
TOTAL field need to be stored in database
SOLUTION QUESTION 2:
is possible if I do this query. Example.
update table1 INNER JOIN table2 ON table1.id=table2.id set table1.field1=table2.fieldX
It is more common to pull out the invoice you want to work on, do the maths in the code to update the values, then put all the updated values back in the database in one go.
There are a thousand different ways of doing this!
possibly one place to start is http://code.msdn.microsoft.com/eisk
or here http://www.asp.net/data-access/tutorials/an-overview-of-inserting-updating-and-deleting-data-vb
In answer to Question two. Unless there is some special need to store the calculated result in the database I would just calculate it each time I wanted to do anything with the total.
This may not scale if you are writing StackOverflow, but for normal use it saves a place where data can slip out of sync.
Get rid of the calculated fields if possible.
update invocies
set percentageTax1 = IIf(percentageTax1=10, 20, percentagetax1)
, percentageTax2 = IIf(percentageTax2=5, 7, percentageTax2)
where idInvoice=@number AND (percentageTax1=10 OR percentageTax2=5)
精彩评论