Update Query to Divide Amounts in Table by 100

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

orbojeff - 20 Nov 2005 23:27 GMT

A field in my Table contains Dollar Amounts that are off; instead of
12.44, it's 1244
Therefore I would like to divide this field by 100

I want to permanently modify the Table, no create a separate Table
I was told the best way to do this is with an Update Query

I'm unfamiliar with this type of query.
Can someone send me a high level summary or an example

Thanks
Jeff

Reply to this Message

Chris2 - 20 Nov 2005 23:59 GMT

> A field in my Table contains Dollar Amounts that are off; instead of
> 12.44, it's 1244
[quoted text clipped - 8 lines]
> Thanks
> Jeff

Jeff,

The basic syntax of an UPDATE query is as follows.

UPDATE <table>
SET <column>
|,<column>...|
WHERE <criteria>

Example

CREATE TABLE OrderDetails
(OrderDetailID AUTOINCREMENT
,OrderID INTEGER
,OrderPlaced DATETIME
,ProductID INTEGER
,Quantity INTEGER
,CONSTRAINT pk_OrderDetails PRIMARY KEY (OrderDetailID)
)

Note: The FOREIGN KEY CONSTRAINTs for OrderID and ProductID have
been omitted for brevity.

Sample Data:

1, 5, 10/29/2005, 10, 1

Query:

UPDATE OrderDetails
SET Quantity = 20
WHERE OrderID = 5
AND ProductID = 10

Output:

1, 5, 10/29/2005, 10, 20

In your case, it would be:

UPDATE UnknownTable
SET DollarAmount = (DollarAmount / 100)
WHERE <criteria>

As always, backup your data first . . .

Sincerely,

Chris O.

Reply to this Message