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

Question

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
        ");

        ?>

Your question title is "Delete duplicate rows but keep the last added" and your SQL is SELECT. Is it irony? — Hakan SONMEZ
– Hakan SONMEZ, Commented Oct 3, 2016 at 19:19
I tried to get the duplicate names to filter by date and to delete , if you can get it. — user3255412
– user3255412, Commented Oct 3, 2016 at 19:35
Do you think your table name is new_table? You have to use your table name instead of new_table? Your table name is tables123. Just change only two strings in my query. Also I have edited your post about my query. — Hakan SONMEZ
– Hakan SONMEZ, Commented Oct 3, 2016 at 20:34
@HakanSONMEZ i updated the code but still i get the same results the same data in table. — user3255412
– user3255412, Commented Oct 3, 2016 at 20:41

Hakan SONMEZ · Accepted Answer · 2016-10-03 21:32:52Z

1

Try 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.

edited Oct 3, 2016 at 21:32

answered Oct 3, 2016 at 19:09

Hakan SONMEZ

2,2342 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayvee · Accepted Answer · 2016-10-03 19:11:19Z

0

something on these lines should work:

delete t from table t 
join (select value, max(Date) maxdate, from table group by value) as t1
on t.value=t1.value 
where date<maxdate

answered Oct 3, 2016 at 19:11

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

Table

Duplicate Values

Value that i want to keep

Update

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Table

Duplicate Values

Value that i want to keep

Update

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related