CODEWITHSUNDEEP
sqlsql-servervb6
SkyDeeper
SkyDeeper

Reputation: 3

SQL Server: update a field with divided and multiplied values from other field on the same table

I want to update a field from my table with a value that comes from: Transaction_Count field : 10 x 100 For example the Transaction_Count value: 3. Then the calculation should be: 3 : 10 x 100 = 30

But when I run the code, the result is 0 without leaving error message. As additional information I already created Support field of my Mining table with decimal(18,2) data type. How to get the correct result, can anybody help me?

Here's my code:

SQL = "Update Mining Set Support = Transaction_Count / 10 * 100"
Con.Execute (SQL)

Upvotes: 0

Views: 916

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

SQL Server, as you have discovered, does integer division. You can just rearrange the operands:

Update Mining
    Set Support = (100 * Transaction_Count) / 10;

Your expression was being evaluated as:

 (Transaction_Count / 10) * 100 =  (3 / 10) * 100

The 3 / 10 is 0 (and NOT 0.33333333) in SQL Server, because it does integer division.

Upvotes: 1

Related Questions