Update several rows with different values SQL

Question

UPDATE table 
SET 
   amount -= '$amount' 
WHERE 
   type = '1' AND 
   amount - '$amount' >= '0'

Okay, let´s explain. If I have two rows in my table:

type | amount
1    | 30
1    | 20

Altogether I want to subtract whatever $amount is, from rows where type is equal to 1. So if $amount holds number 40, that means that I altogether want to subtract 40 and get this result:

type | amount    
1    | 0    
1    | 10

(30 from row 1 and 10 from row 2, that means 40 has been subtracted)

So if one row doesn't cover the number in $amount I want to continue subtracting on another row. But if not even every row together cover $amount, no subtracting shall be made.

Which is the easiest way to manage this?

I use PHPMyAdmin.

Thanks for your help!

In PHPMyAdmin it says MySQL client version: 5.0.32 if that is what you mean. — Daniel
– Daniel, Commented Jul 18, 2011 at 18:42
And does the order of the operations matter? That is, which line should be set to 0 first? Also, what happens if the total amount is less than the sum of the available amounts? Oh, and please tell us you have some sort of unique id column, or I expect this will be otherwise impossible. Although, you may be better off recording some sort of 'discount' type, with a negative value... — Clockwork-Muse
– Clockwork-Muse, Commented Jul 18, 2011 at 18:49

Alan B. Dee · Accepted Answer · 2011-07-18 19:40:54Z

1

If I understand what your trying to do, it is too complicated for the SQL language: or at least too complicated to be practical.
As I see it you have two options that I can think of:

1) You can either retrieve all the rows with id=1 from the database and in PHP modify them accordingly and update each of them afterward. This will be the easiest.

2) Create a user defined function in your database that does the processing. This will be the safest and most efficient but difficult to implement depending on your database.

answered Jul 18, 2011 at 19:40

Alan B. Dee

5,6565 gold badges36 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edgar Velasquez Lim Over a year ago

Too complicated to be an intelligent solution except in specific situations which don't come to mind =D. It's possible and not too difficult but definitely not recommended. Check out my crack at it with a stored procedure.

Marc B · Accepted Answer · 2011-07-18 18:44:28Z

0

Something like this (assuming MySQL) would be the beginnings on it. You'd need to use a variable to keep track of how much of the original amount is still available. This'll need some refinement to handle edge cases (if any), and I'm going off the top of my head, so most likely won't work at all (my brain is mush today):

select @available := 40;

UPDATE yourtable
SET @newavailable :=  amount - @available, amount = amount - @available, @available := @newavailable
WHERE @available > 0;

answered Jul 18, 2011 at 18:44

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

Dirk Over a year ago

I don't think this logic works. Using the OP's example: @newavailable = 30 - 40 = -10, amount = 30 - 40 = -10", @available = -10. Which won't be set because of the WHERE` clause, but the logic should be run (with correct results). Unless I'm missing something.

Marc B Over a year ago

true enough. There'd have to be an IF() of some sort to handle the remainders like that.

Edgar Velasquez Lim · Accepted Answer · 2011-07-19 04:08:32Z

You can use a stored procedure to do this, it would look something like this:

SET @totalamount = 40; (x amount 40 is example)

IF @totalamount - (select sum(amount) from table where type =1) < 0 THEN return;

ELSE @count = 0; WHILE(@totalamount > 0) DO

SET @recordamount = SELECT amount FROM table ORDER BY somevalue limit @count,1;
SET @identifier = SELECT primarykey FROM table ORDER BY somevalue limit @count,1;

@totalamount - @recordamount;

IF(@totalamount < 0)
UPDATE table SET amount = -@totalamount WHERE primarykeycolumn = @identifier;
ELSE
UPDATE table SET amount = 0 WHERE primarykeycolumn = @identifier;
END IF;

@count = @count + 1;

END WHILE; END IF;

You will need to choose a variable to order the table by which will determine what order the calculation will be performed in, and you need a primary key to identify records. This is definitely a terrible way to do this and it would be a better idea to do it programatically by loading results of the SQL query into an array. If there's a really good reason to keep everything in SQL this, probably with some modification? will do it.

Collectives™ on Stack Overflow

Update several rows with different values SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related