使用where子句删除行

Question

I have a table name forum, the table looks like this;

id      studentid    description    teacherid   
1        abc123         hi           zxc123
2        abc123        hello         qwe321

I want to delete a row, what I tried is,

<?php 

require_once('Connections/dbcon.php');

mysql_select_db("school", $dbcon);

$student_id=$_GET['student_id'];
$delete=mysql_query("DELETE from forum WHERE student_id='$student_id'")or  die(mysql_error());

echo "<script language='Javascript'>alert('The Forum Successfully Deleted!');

 location.href='tviewforum.php'</script>";   
 ?>

I want the specific message in a forum. If a student make 2 forums, I want to remove 1 of them and keep the other 1.

Answer 1

you can retain maximum id when there are more than 1 forum created by a student

you can use group by and having to get the maximum id and delete other rows.

delete T from Table1 T
join ( select max(id) as id , studentid
       from Table1
       where studentid ='abc123'
       group by studentid
       having count(*) >1
      ) NewT
on T.id <> NewT.id
AND T.studentid = NewT.studentid