CODEWITHSUNDEEP
sqlsql-server
Alexandre Calvario
Alexandre Calvario

Reputation: 153

Remove quantities from several rows with a certain order

I want to write down a update/delete query that allows me to ask to remove x quantities and the query will delete/update the rows while it has values.

For example the table bellow (Its a simplified version from mine, just to get the idea) I am some other validations that I want to make but this is only my main question.

╔═══════╦═════╗
║ Rowid ║ qtd ║
╠═══════╬═════╣
║ 1     ║ 2   ║
╠═══════╬═════╣
║ 2     ║ 1   ║
╠═══════╬═════╣
║ 3     ║ 3   ║
╠═══════╬═════╣
║ 4     ║ 4   ║
╠═══════╬═════╣
║ 5     ║ 3   ║
╠═══════╬═════╣
║ 6     ║ 5   ║
╚═══════╩═════╝

Imagine I write a query to delete 9 quantities here.

the end result should be something like.

╔═══════╦═════╗
║ Rowid ║ qtd ║
╠═══════╬═════╣
║ 1     ║ 2   ║
╠═══════╬═════╣
║ 2     ║ 1   ║
╠═══════╬═════╣
║ 3     ║ 3   ║
╠═══════╬═════╣
║ 4     ║ 3*  ║
╠═══════╬═════╣
╠═══════╬═════╣
╚═══════╩═════╝

What happened here:

I wanted to see if I could have some viable option directly in sql. to avoid doing some ugly hardcoding using some other options.

And by having a clean sql Option I could save this a learn more.

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You can use a cumulative sum and some logic:

select rowid,
       (case when running_qtd < 5 then 0
             when running_qtd - qtd < 5 then running_qtd - qtd
             else qtd
        end) as amount_left
from (select t.*, sum(qtd) over (order by rowid desc) as running_qtd
      from t
     ) t;

This includes all rows and puts the remaining qtd in the column.

Upvotes: 2

Related Questions