0

I am writing a stored procedure that is called through an api on .net core. The procedure seems to be updating a value only once, when it should update it twice. I am using SSMS 18 on windows 10 servers. Here is the code

UPDATE Inventory.Embarques 
SET Percentage = ((a.TotalKilos - b.kilos)*a.Percentage)/a.TotalKilos,
    TotalKilos = a.TotalKilos - b.kilos
From Inventory.Embarques a
INNER JOIN dbo.PedidosBorrados b 
ON  a.EmbarqueId = b.EmbarqueId
where b.Actualizado = 0

So to be more specific, there are are two rows on table b that should be use to update Percentage and TotalKilos. This value let's say starts at 5.8% and 348kg then should be updated to 2.6% and 156 kg (decreasing the kilos by 192 from the first row referencing this in table b) and then it should be updated to 0 % and 0 kg (decreasing kilos by 156 kg by using the value on the second row referencing this on table b).

Sadly, it seems to only update once and I am not sure what I am doing wrong. I thought the rule about an update not working on the same row twice with an update where command was based off of the update resulting in the same value twice. Thank you for your time and any help you can provide!

EDIT: @Larnu thank you for your input. The plan is not to store aggregate values but rather have a value that is decreased by others. Embarque contains the total amount of kg of a certain order. Then, when parts of it are modified, deleted or delivered these values would affect the total.

Example Order A has 100 kg of a product

This product is going to be sent in two shipments. Shipment 1 will send 75 kg, while Shipment 2 will send 25 kg.

When Shipment 1 is sent, then someone presses a button and this 75 will be deducted form the starting 100, leaving the new total as 25 kg. Then when shipment 2 is made, this person hits another button and the shipment should now have 0kg.

The case I am dealing with is when both shipments have been made before the button is pressed. The process is correctly recognizing both shipments and setting the correct flags, but the update is only happening with the first shipment. As in, if I select from PedidosBorrados I can see the two rows with the correct data, including the flag used for filtering ---Actualizado---

AhOkCasiCasiCasi
  • 1
  • 2
  • Considering the use of the `dbo` schema, I *assume* this is SQL Server *not* Mysql, and so have removed the latter's tag. Also, use *meaningful* aliases. `a` is for `Embarques ` and `b` is for `PedidosBorrados ` wouldn't be my choices; `E` and `PB` seems much more appropriate. [Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Thom A Sep 08 '22 at 14:28
  • which rdms exactly are you using and pleas provide data, if only one gets updated the join only has one row with actualizado = 0. so run first a select and see what results you get – nbk Sep 08 '22 at 14:28
  • 1
    That's not how updates work. You need to aggregate before updating. – Andrew Sep 08 '22 at 14:30
  • If you are planning to store aggregate values in a table, I urge you to reconsider. This is *normally* a bad idea as the values will be wrong as soon as the other table has some kind of `INSERT`/`UPDATE`/`DELETE` against it. If you want aggregate values in an easily consumable location, consider using a `VIEW`. – Thom A Sep 08 '22 at 14:33

0 Answers0