Deleting duplicate values

[micmac]

Hi all,
The following code checks the DB for duplicate actor_name.

// ------- DELETE DUPLICATE ACTOR NAMES
$sSQL = "SELECT actor_name FROM actor GROUP BY actor_name HAVING Count(actor_name)>1";
$result = mysql_query($sSQL) or print(mysql_error());
$total = mysql_num_rows($result);
echo "<b>RESULTS:</b> $total<br><br>";	
while($alldata = mysql_fetch_assoc($result)){
$actor_name = $alldata["actor_name"];
echo "$actor_name<br>";
}

What i need to know is how can i delete these duplicate entries without deleting the first occurance of the name?

Cheers,
micmac

[LordShryku]

Give this a shot. It adds a count of occurances to your original SQL, then performs the delete based on the count - 1

$sSQL = "SELECT   actor_name, 
                  count(*) AS cnt 
         FROM     actor 
         GROUP BY actor_name 
         HAVING   count(actor_name)>1";

$result = mysql_query($sSQL) or print(mysql_error()); 
$total = mysql_num_rows($result); 
echo "<b>RESULTS:</b> $total<br><br>";     

while($alldata = mysql_fetch_assoc($result)) { 
   $actor_name = $alldata["actor_name"];
   $cnt = $alldata["cnt"];
   echo "$actor_name<br>";

   $sql = "DELETE FROM actor 
           WHERE actor_name = '".$actor_name."'
           LIMIT ".($cnt - 1);
   mysql_query($sql);
   unset($sql);
}

[micmac]

Thanks for the reply LordShryku...that worked beautifully.