How to upsert multiple rows atomically?
How do you update multiple rows in one go, making sure each update is atomic and either all succeed or all rollback?
My use case: updating stock for an item across several warehouses.
Each update has a different increment/decrement value, and I need to make sure the final quantity never goes below 0.
Bulk update seems to set the same value for all rows, but what I need is per-row updates that happen safely based on the current db value, all inside a transaction.
Also, what’s the best way to handle cases where the row doesn’t exist yet? I’m guessing I’d need to use some form of upsert to insert the initial value and still keep everything atomic.
My use case: updating stock for an item across several warehouses.
Each update has a different increment/decrement value, and I need to make sure the final quantity never goes below 0.
Bulk update seems to set the same value for all rows, but what I need is per-row updates that happen safely based on the current db value, all inside a transaction.
Also, what’s the best way to handle cases where the row doesn’t exist yet? I’m guessing I’d need to use some form of upsert to insert the initial value and still keep everything atomic.
