Vfrdtyky

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());

}

}

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

Here's apples...

SET @apple = 9;

UPDATE fruits x

  JOIN 

     ( SELECT id

            , apple

            , GREATEST(apple-@apple,0)n,@apple:=@apple-apple 

         FROM fruits 

        ORDER 

           BY id

     ) y

    ON y.id = x.id

   SET x.apple = y.n;