remove values from different rows until data exhaustion?

Question

I have a table like this

+----+-------+------+
| id | apple | pear |
+----+-------+------+
|  1 |   3   |  10  |
+----+-------+------+
|  2 |   5   |   8  |
+----+-------+------+
|  3 |   4   |   7  |
+----+-------+------+

I have to delete 9 apple and 11 pears. A result like this

+----+-------+------+
| id | apple | pear |
+----+-------+------+
|  1 |   0   |   0  |
+----+-------+------+
|  2 |   0   |   7  |
+----+-------+------+
|  3 |   1   |   7  |
+----+-------+------+

Is there a way to do this without having to do a lot of queries?

I did something like this. I wonder if there is a better way.

    $eat["apple"]=9; 
    $eat["pear"]=11;
    $id=0;   //I will increment this id during the cycle

    while ($eat["apple"] > 0 && $eat["pear"] > 0) {
        $get_fruit=mysql_query("SELECT id,apple,pear FROM fruits WHERE 
           id>'".$id."' 
   ORDER BY id LIMIT 1") or die (mysql_error());

        if((mysql_num_rows($get_fruit))==0){
           die; 
        } else {

          $fruit = mysql_fetch_assoc($get_fruit);
          $id = $fruit["id"];   

          foreach ($eat as $key => $value) {

             if ($fruit[$key]>$eat[$key]) {
                $fruit[$key]-=$eat[$key];
                $eat[$key]=0;
             } else {
                $eat[$key]-=$fruit[$key];
                $fruit[$key]=0;
             }
      }

    $update=mysql_query("UPDATE fruits SET 
            apple='".$fruit["apple"]."',
            pear='".$fruit["pear"]."' 
            WHERE id='".$id."'LIMIT 1") or die (mysql_error());
}

}

Answer 1

CREATE TABLE t
    (`id` int, `apple` int, `pear` int)
;

INSERT INTO t
    (`id`, `apple`, `pear`)
VALUES
    (1, 3, 10),
    (2, 5, 8),
    (3, 4, 7)
;

UPDATE t
INNER JOIN (
  SELECT
  id, apple, pear,
  GREATEST(0, apple - GREATEST(0, @atr)) AS new_apples,
  @atr := @atr - apple,
  GREATEST(0, pear - GREATEST(0, @ptr)) AS new_pears,
  @ptr := @ptr - pear
  FROM t
  , (SELECT @atr := 9, @ptr := 11) variable_initialization_subquery /*@atr = ApplesToRemove...*/
  ORDER BY id
  ) sq ON t.id = sq.id
SET t.apple = sq.new_apples,
t.pear = sq.new_pears;

SELECT * FROM t;

| ID | APPLE | PEAR |
|----|-------|------|
|  1 |     0 |    0 |
|  2 |     0 |    7 |
|  3 |     3 |    7 |

• DEMO