MYSQL在PHP循环时更新和删除查询

Question

I am trying to update a user portfolio table to update when a user sells a share of a item. I want to subtract one from shares until the desired number of shares to sell hits 0. Then when the shares of that item reaches 0 I want to delete that row from the users portfolio. I believe I have to use a while loop and the TOP command because users often have made many different purchases of the same item so they are stored in different records in the table. I think am I pretty close but I can not get either the update or delete query in my loop to run.

while ($sharesToSell >0) 
{
 $sql="UPDATE TOP (1) portfolio SET shares= shares-1 WHERE 
 userid='".$userid."'AND songid='".$songID."';";
 $result6 = $connection->query($sql);
 $sharesToSell-=1;
 if($sharesToSell>0)
 {
  $sql="DELETE FROM PORTFOLIO WHERE shares='0';";
  $result7 = $connection->query($sql);
 }
}

Answer 1

update your first query and use limit instead top

 $sql="UPDATE TOP (1) portfolio SET shares= shares-1 WHERE 
 userid='".$userid."'AND songid='".$songID."';";

to

$sql="UPDATE  portfolio SET shares= shares-1 WHERE
 userid='".$userid."'AND songid='".$songID."' limit 1";

use limit instead top