0

I want to do an update with the condition below:

SettlementAmount = CollectionAmount - (CollectionAmount * (3 / 100))

And this is what I'm doing:

 UPDATE [dbo].[QuoteOptions] 
 SET SettlementAmount = CollectionAmount - (CollectionAmount * (3 / 100)) 
 FROM [dbo].[Quotes]
 JOIN [dbo].[QuoteOptions] ON [dbo].[Quotes].Id = [dbo].[QuoteOptions].QuoteId
 WHERE [dbo].[Quotes].PaymentProfileId = 43 
   AND SettlementAmount = CollectionAmount

When I run this query, I get a message that two rows have been affected, but they are not actually! Could anyone be so kind as tell me what mistake I'm making?

collectionAmount is = 100
settlementAmount = 100

I want to make

settlementAmount = collectionAmount - (100 * (3 / 100))
jarlh
  • 42,561
  • 8
  • 45
  • 63
csharper
  • 223
  • 1
  • 2
  • 10
  • 1
    Turn the UPDATE into a SELECT to see what comes back, what the current value is, and what the updated value would be. Then you can better see what is actually being written (and why it is not what you expected it to be). – Igor Aug 11 '22 at 13:15
  • @Igor how do you mean? by turning? – csharper Aug 11 '22 at 13:16
  • 1
    Probably you are using SQL Server which does integer division between integers and this means that 3/100 returns 0. Change to `3.0/100` – forpas Aug 11 '22 at 13:16
  • `update [dbo].[QuoteOptions] set SettlementAmount=CollectionAmount-(CollectionAmount* (3/100))...` becomes `SELECT SettlementAmount, CollectionAmount, CollectionAmount-(CollectionAmount* (3/100)) FROM your joins WHERE your filter`. Now you can see what would actually be written and what rows are being affected. This is a good troubleshooting tip for any UPDATE statement (or even DELETE statement). – Igor Aug 11 '22 at 13:17
  • 1
    @forpas hell yes,that was the issue – csharper Aug 11 '22 at 13:18

0 Answers0