Deleting duplicate entires

[micmac]

Hi all,
If i have the following mysql table structure and data:

movie_actor_id  movie_id  actor_id  

1               39936     6357 
2               39936     9212 
3               39936     9969 
4               39936     8591 
5               39936     6357 
6               39936     9212 
7               39936     9969 
8               39936     8591

i am wanting to delete any row where the movie_id and actor_id are duplicates. In the above example they are all duplicates.
How would i delete duplicate entires except for the first interation?

This is what i have so far:

$sSQL = "SELECT   actor_id, 
                  count(*) AS cnt 
         FROM     movie_actor  

         GROUP BY actor_id 
         HAVING   count(actor_id)>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_id = $alldata["actor_id"];
   $cnt = $alldata["cnt"]; 
   echo "$actor_id<br>"; 

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

but this won't work because it is only looking for duplicates in relation to actor_id.
Any ideas most appreciated.

Cheers,
micmac

[crowezr]

I don't know how large your table is, so this may not be the most efficient, but you could load all your actor_ids into an array and then loop through your movie_id query and delete any place where the value in the array equals the movie_id.