<?php
$dbhost = "localhost";
$dbusername = "root";
$dbpassword = "";
$dbname = "url";
$mysqli = new mysqli ($dbhost, $dbusername, $dbpassword, $dbname);
$sql ="DELETE FROM data
WHERE no IN (SELECT *
FROM (SELECT no FROM data
GROUP BY topic HAVING (COUNT(*) > 1)
) AS A
)";
$query = mysqli_query($mysqli,$sql);
if(mysqli_affected_rows($mysqli)) {
echo "<div style=\"font-size:30pt;\">Record delete successfully</div>";
}
mysqli_close($mysqli);
?>
Unknown table 'data' in MULTI DELETE
delete data from data tb left join ( select min(no) mno, topic from data group by topic ) tbm on tb.topic = tbm.topic where tbm.mno<>tb.no
Code (PHP)
$sql ="delete data
from data tb
left join (
select min(no) mno, topic from data group by topic
) tbm
on tb.topic = tbm.topic
where tbm.mno<>tb.no
";
$mysqli->query($sql) or die ( $mysqli->error . "<br>$sql");
if($mysqli->affected_rows) {
echo "<div style=\"font-size:30pt;\">Record delete ".$mysqli->affected_rows."</div>";
}
ขอบคุณค่ะ
แต่ยังขึ้น error
Unknown column 'y' in 'field list'
DELETE tb FROM data tb, ( select min(y) mno, x from data group by x ) as tbm WHERE tb.x = tbm.x AND tbm.mno<>tb.y