0

Am using MySQL 5, need to write a SQL statement(or stored procedure if needed) to delete all duplicate rows from this table for a given device except for the most recent row (youngest created_time)?

Here is the table:


desc user_detail;

Yields:

| Field           | Type         | Null | Key | Default             | Extra                       |
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| user_id         | varchar(200) | NO   | PRI |                     |                             |
| device          | text         | YES  |     | NULL                |                             |
| created_time    | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| updated_time    | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
PacificNW_Lover
  • 4,746
  • 31
  • 90
  • 144
  • I see that device is nullable, what do you want to happen when device is null? – Bryan Newman Jun 27 '17 at 04:51
  • @BryanNewman Good question... This table was populated from a different Rest call so device can be null but there were a lot of dups which made us wonder about this specific query. Thanks for asking. – PacificNW_Lover Jun 27 '17 at 04:59

1 Answers1

1

This can be done with an exists self join:

delete from user_detail a where exists (
    select 1 from user_detail b
    where a.device = b.device
    and b.created_time > a.created_time
)
Bryan Newman
  • 621
  • 3
  • 9