How do I write a query to delete duplicates in a table?

Question

Given a table resembling this one, called VehicleUser:

VehicleUserId | VehicleId | UserId
1                     | 1001       | 2
2                     | 1001       | 2
3                     | 1001       | 2
4                     | 1001       | 3
5                     | 1001       | 3
6                     | 1001       | 3

How do I write a query that can delete the duplicates? row 2 and 3 are identical to row 1 except for a different VehicleUserId and rows 5 and 6 are identical to 4 except for a different VehicleUserId.

There are a few good answers below which will do what you're asking for, however, I would also recommend you think about what the VehicleUserId is there for. This kind of join table looks like exactly the kind of thing where a composite primary key is appropriate, especially as you're asking how to remove these duplicates. You might want to restructure your table to avoid having to do this in the future. — dataduck
– dataduck, Commented Jan 25, 2011 at 16:30
In a somewhat related side-note: this is a good example of why not all tables need an IDENTITY (or Sequence) field. In this case, the PK should be a composite of VehicleId and UserId. This would have been a cleaner data model and would have prevented the need for this question as it would be impossible to get duplicates :). — Solomon Rutzky
– Solomon Rutzky, Commented Jan 25, 2011 at 16:31
A single column is more convenient to use as a reference. For how do you know that VehicleUser cannot possibly be referenced too? But for integrity's sake there should probably be created a unique index on the pair of VehicleId & UserId. — Andriy M
– Andriy M, Commented Jan 28, 2011 at 9:05
@Andriy: that is a common mistake that people make. There is rarely a reason to abstract the UserID / VehicleID relationship via an additional meaningless IDENTITY field. By using a composite PK it both maintains the data integrity AND is what gets referenced in other tables. Foreign Keys can certainly point to composite PKs. Adding a Unique Index in this case just adds more schema and disk I/O to make up for a poor modeling decision. — Solomon Rutzky
– Solomon Rutzky, Commented Jan 30, 2011 at 22:23
@srutzky: No argue about that where 'vehicle + user' abstraction is indeed unnecessary. Yet you yourself seem to accept the fact that sometimes the abstraction is needed. I know of a project where such an abstraction, called 'Driver', takes place and seems relevant so far. Although now I'm starting to wonder if it wouldn't be better off without Driver... — Andriy M
– Andriy M, Commented Jan 31, 2011 at 6:12

Andomar · Accepted Answer · 2011-01-25 16:30:48Z

6

;with cte as ( select row_number() over (partition by VehicleId, UserId order by VehicleUserId) as rn from VehicleUser) delete from cte where rn > 1;

edited Jan 25, 2011 at 16:30

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered Jan 25, 2011 at 16:26

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

Comments

Andomar · Accepted Answer · 2011-01-25 16:24:53Z

You could filter duplicates with a exists clause, like:

delete v1 from VehicleUser v1 where exists ( select * from VehicleUser v2 where v1.VehicleId = v2.VehicleId and v1.UserId = v2.UserId and v1.VehicleUserId > v2.VehicleUserId )

Before you run this, check if it works by replacing the delete with a select:

select * from VehicleUser v1 where exists ( ...

The rows that show up will be deleted.

Beth · Accepted Answer · 2011-01-25 16:29:09Z

here's your unique values:

select vehicleid, userid, min(vehicleuserid) as min_id from vehicleuser group by vehicleid, userid

you can put them in a new table before deleting anything to make sure you have what you want, then delete vehicleUser or use an outer join to delete rows from vehicleUser that aren't in the new table.

Debugging before deleting rows is safer.

Hey thanks for the min(column) trick - I didn't think there would be a way to do this.

Neil Barnwell · Accepted Answer · 2011-01-25 16:25:01Z

I don't think you can do this purely in a single query.

I'd do a grouped query to find the duplicates, then iterate the results, deleting all but the first VehicleUserId row.

select VehicleId, UserId from VehicleUser group by VehicleId, UserId having count(*) > 1

Will get you the VehicleId/UserId combinations for which there are duplicates.

Collectives™ on Stack Overflow

How do I write a query to delete duplicates in a table?

4 Answers 4

Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Related