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' )
CodePudding user response:
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.