How to get rid of duplicates in a table

How does one even know they have duplicates ? or to what extent ? You can use this SQL statement right here:

The key here is that we are using the “group by” clause to aggregate a bunch of data. And after that we are using the “having” filter clause, to add the filter “count(title) > 1” which is just saying “title found more then once”

Now that we have made sure that we actually do have duplicates, lets get down to deleting them.

The key here is that we are using aliases rather than joins or something else. So you can think of “dupes” and “fulltable” as variables. First we set the values of these variables, then we use the where clause to filter the things we are looking for. At this point if we just ran the query, we would just end up deleting our whole table. Therefor we have a final “and” where we specify that we only want to select the id that greater out of the two, rows that were in “dupes” and “fulltable”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s