Update query (access & vb.net)

Question

Hi:

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 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

Answer 1

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

Answer 2

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.

Answer 3

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)