How to delete duplicate rows including the first row as well?

user_16179262 0
Viewed 52

I have a table with columns

Car   |   User  |  Location  |  Time  |  Type

I want to delete all rows that have duplicates, leaving only rows that are distinct based on the car, user, location, time columns

For example:

Car   |   User  |  Location  |         Time          |  Type
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 03:12:45  |   OUT        //should be deleted
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 03:12:45  |   IN         //should be deleted
-----------------------------------------------------------------
  2   |    Sam  |  New York  |  2022-02-12 04:42:45  |   OUT        //should be kept
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 04:50:00  |   OUT        //should be kept

To make it easier for help

CREATE TABLE rent_logs (
  car varchar(30),
  user varchar(30),
  location varchar(30),
  time datetime,
  type varchar(10)
);

INSERT INTO rent_logs ( car, user, location, time, type ) 
VALUES ( 1, 'Ben', 'Chicago','2022-02-12 03:12:45',  'OUT' ), 
( 1, 'Ben', 'Chicago',' 2022-02-12 03:12:45',  'IN' ), 
( 2, 'Sam', 'New york','2022-02-12 04:42:45',  'OUT' ), 
( 1, 'Ben', 'Chicago','2022-02-12 04:50:00',  'OUT' ), 
( 2, 'Sam', 'New york','2022-02-12 07:32:12 ',  'IN' ), 
( 1, 'Ben', 'Chicago','22022-02-12 08:18:45',  'IN' ), 
( 3, 'Mia', 'Chicago','2022-02-12 09:12:43',  'OUT' ), 
( 1, 'Ben', 'Chicago','2022-02-12 09:27:23',  'IN' )
sql-delete join mysql sql database
1 Answers

Join the table to a query that aggregates in rent_logs and returns all the rows with duplicates:

DELETE r
FROM rent_logs r
INNER JOIN (
  SELECT car, user, location, time
  FROM rent_logs
  GROUP BY car, user, location, time
  HAVING COUNT(*) > 1
) t ON (t.car, t.user, t.location, t.time) = (r.car, r.user, r.location, r.time);

See the demo.

user_104988280
Related