How to subtract one value from multiple rows of same column in SQL C#

Question

1.33/5 (3 votes)

See more:

i want to subtract 40kg rice from qty column date wise like from 1st row 10 and 2ed row 25 and so on

ing_id|qty|Price|Date       |unit |name
01    |10|50    |12/12/2018 |kg   |Rice
01    |25|56    |12/14/2018 |kg   |Rice
01    |30|70    |12/20/2018 |kg   |Rice

and update the table like :

ing_id|qty|Price|Date       |unit |name
01    |00|50    |12/12/2018 |kg   |Rice
01    |00|56    |12/14/2018 |kg   |Rice
01    |15|70    |12/20/2018 |kg   |Rice

hepl!! Thanks in Advance

What I have tried:

I dont undarstand how to do!
any suggestion
or help

Posted 12-Dec-18 1:04am

Member 14086748

Updated 22-Jul-21 23:10pm

Maciej Los

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 12-Dec-18 8:09am

1st row 10, and second row 25. Where are these conditions coming from?

Most likely, you need to perform a SELECT...UPDATE query. Or a nested SELECT within the UPDATE query.

Member 14086748 12-Dec-18 9:54am

from the database

Afzaal Ahmad Zeeshan 12-Dec-18 12:38pm

How?

Member 14086748 13-Dec-18 7:20am

ing_id|qty|Price|Date |unit |name
01 |10|50 |12/12/2018 |kg |Rice
01 |25|56 |12/14/2018 |kg |Rice
01 |30|70 |12/20/2018 |kg |Rice
from the table

3 solutions

Solution 1

Afzaal Ahmad Zeeshan is connect, I'm afraid.

In SQL you can aplly changes to multible rows only if the command is the same for every row! Thus applying different substractions for specific rows you'll have to define a specific command for each substraction.

The only exception would be if you could define a general equation which uses the table data as input - e.g. by using a sub-select getting the average value to subtract and then using that to apply a calculated value to each row. But for that I'm afraid you didn't give enough information to find such a way.

Posted 12-Dec-18 2:12am

Bjørn

Solution 4

CREATE TABLE #table(ID int, Qty Decimal(11,3))
INSERT INTO #table(ID,Qty)
VALUES	(1, 1),
		(2, 5),
		(3, 5),
		(4, 10);

Declare @q int = 12;

SELECT id, Qty
       ,(@q - cumvalue1 ) as RemainingQtyForNextRow
       ,(CASE When @q - cumvalue1 > 0 THEN 0 ELSE  cumvalue1 - @q  END) as RemainingQty
FROM (SELECT id, Qty,
       sum(Qty) over (order by id) as cumvalue1
      FROM #table t
     ) t;

Posted 22-Jul-21 23:10pm

SamirChavan23

Comments

CHill60 23-Jul-21 5:18am

A code dump with no commentary is not a good solution - especially when the table you have created does not resemble the original post neither in schema nor content

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Reno89512 · Accepted Answer · 2018-12-13T09:56:00

actually, 3rd qty in the 2nd table should be 25 instead of 15 for the 40kg question versus 50kg. if this is going to be an ongoing thing, create a stored procedure to walk through the rowset given sku, qty, with some error handling for over/under qty available.. or just figure out the stored procedure and code it at the VS/C# level. that's a cool request, as usual, necessity is the mother of invention. need the stored procedure. , let me know.