user3255412 user3255412 - 2 months ago 9
MySQL Question

PHP - MYSQL - Delete duplicate rows but keep the last added

How to delete duplicate rows but to keep the last inserted.

Table



Name Surname Value Date
A AA 2 2014-10-01
B BB 5 2014-12-01
C CC 9 2015-07-01
D DD 9 2016-10-01
E EE 9 2014-10-25


Duplicate Values



Name Surname Value Date
C CC 9 2015-07-01
D DD 9 2016-10-01
E EE 9 2014-10-25


Value that i want to keep



Name Surname Value Date
D DD 9 2016-10-01





The code updated after the answers, The goal is achieved.
Thanks to all respondents.
I hope this will be useful for someone.

Update



<?php
include("conf.php");
$table = "tables123";

$query = "SELECT Name FROM " . $table;
$resultat = mysqli_query($conn,$query);

if(empty($resultat)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $table (
Name varchar(255) NOT NULL,
Surname varchar(255) NOT NULL,
Value varchar(255) NOT NULL,
Date varchar(255) NOT NULL
)CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else

$sql = "INSERT INTO $table (Name, Surname, Value, Date)
VALUES
('A', 'AA', 2, '2014-10-01'),
('B', 'BB', 5, '2014-12-01'),
('C', 'CC', 9, '2015-07-01'),
('D', 'DD', 9, '2016-10-01'),
('E', 'EE', 9, '2014-10-25')
";


if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}


/// Hakan SONMEZ solution
$sql3 = mysqli_query($conn, "
DELETE tbl1 FROM $table tbl1, $table tbl2 WHERE tbl1.date < tbl2.date AND tb1.value = tb2.value
");

?>

Answer

enter image description hereTry this.

DELETE tbl1 FROM new_table tbl1, new_table tbl2 WHERE tbl1.date < tbl2.date AND tbl1.value = tbl2.value;

Edit: Copy your data before you try.