How to remove Duplicate Data in SQL | SQL Query to remove duplicate

hey guys tawfiq here now on my channel i have just created three new playlist where in the next couple of months i will be posting several different videos of solving sql queries so basic intermediate and complex sql queries now if you have an sql query that you would like me to make a video about and you would like me to include that sql query in your in this particular playlist then definitely share with me your sql query you can email me your sql query my email id is in the video description now in this video i'm going to be solving a very popular data analytics problem that is how do you remove duplicate data from your database now when i say duplicate data there can be two different scenarios the first scenario is that data is duplicated based on certain columns in your table so not every column has duplicate data but some of the columns in your table has duplicate data okay and the second scenario is that every single column in your table has duplicate data now the solution to remove duplicate data in both the scenarios may be different and i'm going to be giving you multiple different solutions for both of these scenarios so i'm going to give you seven different solutions for scenario one and i'm going to give you three different solutions for scenario two okay now before i can start i would like to mention that all the scripts data sets sql queries everything you can download it for free from my blog the link to my blog is in the video description okay now let's get started okay so let's start by looking at scenario one which is basically data is duplicated based on some of the columns okay now in order to explain this i have created a cars table which has information about some of the electric cars as you can see here this is my table so i have an id column which basically has unique id for every single record then i have the model which basically is the car name then the brand of the car is mentioned here then i have the color of the car and the make of the car okay now let's say you have been given a requirement where you need to write a query to delete duplicate data from the cars table okay and they have told us that duplicate record is identified based on the model and the brand name okay so what this means is not every column needs to have duplicate data in order for the entire record to be considered as duplicated okay even if you can find a records where the model and the brand are duplicated we can treat the entire record to be duplicated and we need to find such duplicate records and we need to remove them from the cars table okay now in this particular table we can see that we have ionic 5 which belongs to the brand hyundai and i have two different records for the same car and for tesla model s again i have two different records okay now based on my requirement for this particular query i can treat that these two records are duplicated and similarly these two records are duplicated even though their ids are actually different okay but based on the model and the brand we can say that these two records are duplicated and these two are duplicated okay now i'm going to give you six different solutions to remove duplicate data from your table and i'm going to start with the solution one that is i'm going to give you a solution by using a unique identifier okay now before you can remove duplicate data from your table the very first thing that you need to do is you need to write a select statement to identify the duplicate records okay so write a select query which is going to fetch the duplicate records and then over that select query you can simply write a delete statement to delete those records right so our first task is to identify the duplicate data from the cars table so i'm just going to say select star from cars okay i'm just going to say star and if i execute i'm getting all of these records now one of the easiest ways to identify duplicate records in a table is by using the group by clause so i'm just going to be using that so i'm just going to say group by model comma brand okay and let's say for now i'm just going to use the same columns in my select clause and i'm just going to say count off star okay and if i execute this now you can see that for every combination of unique combination of model and brand one group is being created and in that group i have applied this count aggregate function which basically is going to count for the combination of these two values how many records are present in the cards table okay so sql is telling that for bmw ix there is just one record for model s which belongs to tesla there are two records hyundai ioniq 5 has two records and mercedes-benz eqs has one record okay now with this i have created a group and i have told you how many records are present in each group right now what i need to do is i only need to fetch the duplicated records right how i can do that is i can just use a having clause and put a filter condition here telling that having of count of a star is greater than one okay so i'll get only the records where for the combination of model and brand there are multiple records in this table so if i execute this you can see that i'm getting the desired two records now i have got this and i know that for each of these records there is two records for this tesla model s and two records of hyundai ioniq phi the id is different for all of these records so for example if i go and query from my cards table you can see that even though ionic 5 has two records their ids are different and even though tesla model s has two records their ids are different and when i'm deleting duplicate records i should not be deleting both these records i only need to delete the duplicated record so among these two tesla model s i have an entry for id1 and i have an entry for id5 i only need to delete this one and i only need to delete this one so how i can do that is using this same query i can use another aggregate function that is max and here i can tell max of id so by using max what i'm telling sql is you only fetch the record where the id is max among this group that is among this ionic 5 the record with the max id is this one right 6 and the same thing will happen for model s right now if i execute this you can see that i am getting the record where the id is five and six and these are the actual records that i want to delete right so now once i have basically got the exact record that i want to delete what i can do is i can just put a delete statement here so i can just tell delete from cars and i can put a filter condition here saying that id is in whatever id that have got from my query here so i'll put this entire thing into a sub query okay and i'm just going to close the parenthesis here and since i'm only matching it with the id i'll remove these two columns because i only need the id here okay so if i execute this i will get the two ids that is five and six and now if i execute this entire query you can see that delete two meaning that two records are deleted and now if i go back and query from my cards table you can see that the two duplicated records have been deleted i now only have unique records in this particular table okay so this is solution one pretty simple right now i think most of you guys would know this solution but generally what happens is when you go to an interview they will the interviewer might not just ask you for one solution they might ask for multiple different solutions so what is another solution of deleting duplicate records from your table okay now the second solution that i'm going to be giving you is by using something called as a self join okay so again before i can delete duplicate records the very first thing that i need to do is i need to write a query to identify the duplicate records right okay but before that since i have already removed the duplicate record i'm again going to repopulate this table with the duplicate records and that is by using this insert statement so i'm just going to insert duplicate entries again okay and now if i look into my cars table you can see that again the duplicate data has been inserted so now let's go into my solution to that is using self join so again i need to write a query to identify duplicate records right so let's try to do that and since i'm telling i'm going to be using a self join what i'll do is i'll fetch the data from my cards table i'm going to call it like c1 and i'm going to join the same table to itself okay and i'm going to say cars again i'm going to give an alias for this like c2 and now i need to give a join condition so i know that my final intention is to find those records where the data is duplicated right so i can actually join these two tables using the duplicated columns that is model and brand right so i can just tell seed one dot model equal to c2 dot model okay and c1 dot brand is equal to c2 dot brand okay this is fine okay now what happens is uh basically i will join the same table to itself and wherever the model and the brand are same it's going to make a join but the problem is i do not want the same record of c1 to match with the same record of c2 right because if i just do this every single record gets matched so if i run this you can see that i'll get many many records i'm getting 10 records i don't want that to happen so i want to add a filter condition here telling that when i'm doing a match for example if i have to show it to you more clearly using an excel file okay so i have this symbol diagram here so i have this you can consider like my c1 and this is c2 basically they are the same table since i'm doing a self join i'm joining the same table to itself and i'm joining it based on the model and the brand column right so i was hoping that only the duplicated data will get matched but the problem is this let's say eqs is also present here and here so even that will get matched ix is present here as well as here so basically every single record from this table will get matched with every single record from this table right i don't want that to happen i only want to match the duplicated record so what i can do is i can use this id column so i can tell only make a join so for example i can just tell c1 dot id is less than c2 dot id okay and let's say if i run this now you can see that i'm only getting the duplicated record and from here instead of fetching the data from both the tables i can just tell c2 dot star okay and now if i run this you can see that i'm getting only these two record with id five and six these two are the duplicated record how did this happen is if i go back to excel when sql is processing it's going to take the first record from c1 and it's going to match it with all the six records from c2 okay since i have this filter condition saying that c1 dot id is less than c2 dot id so this id is one and this id has to be less than the id from the c2 table so this record will not get matched but all these five records will get matched okay so if i just have to highlight i will just put a mark here i will just tell something like this record will get matched with all these five records and even here what happens is not every record gets matched because the joining is based on model and brand right so among these five records sql is going to check model s where is model as present so this one so this one will only match with this one right so let's say if i highlight this here uh so basically this both okay so this record here will only get matched with this record here okay and that is why it's only going to return this model s record okay similarly from the first record when it's going to process here and here it's not going to find any match okay but when it comes to ionic 5 it's going to find a match okay so i hope you get an idea of how self join works so basically by writing a query something like this i'm able to fetch the data that i want and from here i don't need to fetch every single column i am only interested in the id okay so i'm just going to say c2 dot id that is 5 and 6 and then again like what i did in previous solution i can just say delete from cars okay where id is in this whatever id that i have got from myself join sub query okay so if i just put that here and if i put a semicolon here and if i run this i think two records will get deleted and now if i go back to my cards table you can see that i'm only having the unique record so duplicate records have been deleted okay so this is solution two i hope this is clear by using a self join and this method will probably okay in fact both this method will work in any of the rdbms okay now let's go into solution three and we are going to be using a window function okay so let's see how i can delete duplicate records using window function again the very first step that i need to do is to write a select statement to identify the duplicated records right so i'm just going to say select star from cars okay that is star and let's say if i execute this i'll get this okay so i i deleted the duplicate records in the previous solution so first of all let me repopulate the duplicate entries by running this insert and now let me run my select statement so now i have the duplicate records so now what i need to do is i'm going to be using window function to delete duplicate records first of all let's identify the duplicate records now the window function that i'm going to be using here is called as row number so what i'm going to be doing is i'm going to basically partition the data based on this duplicated column that is model and brand and then i'm going to give a row number okay wherever there are duplicate records the row number is going to be basically there will be a record with row number one and there will be other records with row number greater than one okay and then i can only filter the data based on that row number greater than one right so what i'll do here is i'll just put an over clause and i need to partition that data based on this model and brand right so i'm just going to say partition by model comma brand okay and then i'll say okay maybe order by is not required in this particular case because i'm not bothered in which order they get sorted and here i'll just give a name like rn and now if i run this now you can see that for this ionic five you can see i have one record with the row number one and the duplicated record is having the row number two okay and the same thing with tesla model s there is the original record has row number one and the duplicated record has row number two right now what i can do is i can just put this into a sub query so i can just tell select star from okay i can open a parenthesis here and let me move this to the right and let's say i'll give an alias for this x and here i can just tell x dot rn is greater than one okay so even if there are multiple duplicate records this method should work okay and now you can see that i'm getting the duplicated records and here again i'm only interested in fetching the id column so i'm just going to fetch the id column here and if i run this you can see i'm getting five and six again in order to delete these records i can just use a simple delete statement so delete from cars okay where id in this whatever id that have got from this sub query right so i'll just close this given alias for this okay maybe not required and i think that's all now if i run this you can see that two records have been deleted so if i go back and query from my cards table you can see that i have removed the duplicate records okay so this is the solution three using window function i am actually going a little faster the reason is because i need to show you 10 different solutions and i think these are all pretty simple queries you should be able to grasp now let's move on to the solution for so the next solution that i'm going to give you is actually a very good solution so this is a solution that would work even if you had multiple duplicate records okay now before i get into this solution the first thing that i'll do is i'll repopulate the duplicate records by running this insert statement and now i'll query the data from the cards table now you can see i have the duplicate data so the fourth solution is by using the minimum aggregate function and as mentioned here this will delete even if there are do multiple duplicate records okay so for example i will run the insert statement one more time okay so hopefully there should be multiple duplicate entries now now you can see i have eight records in this table so you can see ionic five is now repeated three times model s is repeated three times right so let's look at this solution for now again the first thing that i generally have done is to identify the duplicate records right but in this case we'll try to do it slightly differently instead of identifying the duplicate records let's try to identify the unique records first okay so kind of like we let's try to find out the original record first and then whatever records does not belong to this original list we'll try to delete them okay so how we can do that is i'm just going to say select star from cars i'll just tell group by model com brand okay and let's say if i am going to put it in the select clause and if i run this you can see that i'm basically getting all of this model and card names here okay and from this if i just do an aggregate function saying that minimum of id okay minimum of id will basically be the id of the very first record that was inserted right so if i run this now you can see that the id that i have got is one two three and four so looks like these were the first four ids that got inserted into the table so i want to maintain these four records but any other record present in the table other than these four ids i want to delete them okay this is the logic that i will be using so what i can do here is i can just tell select star from cars okay where id not in whatever the list that i have got okay so here if i just uh execute this okay so i don't need these columns here okay and now if i just run this now you can see that i have got this duplicated entries that is model as ionic five you can see the id is five six and five six because i did multiple inserts here okay so this is basically one way if you have a table with multiple duplicate records then i think this is a better way of doing that you can also do that by using row number function what i showed previously but this is also one way of doing that okay now again in order to delete this statement i can just tell delete uh okay not here i still need this so i can just tell select id okay from the cards i have this id so then i can put a delete statement here telling that delete from cards where id in okay so i can have this i think this is redundant i can basically remove this okay so what i'll do is okay again i'll just remove this delete statement here and instead of having a select statement here i can just tell delete from cars where id not in this one okay so here i got the four ids which basically is the id of the original record any other i any other ids that got inserted into the table are duplicated records so i'm just going to include only those ids and i'm going to delete them okay so now if i execute this you can see four records have been deleted so if i go back to my main query select star from cars you can see that these are the four records basically the unique records that are preserved in the table okay so this is actually solution four where you can use the minimum function to remove not just one duplicate records but multiple duplicate records i hope all of this is clear now let's come to our fifth solution that is by creating a backup table okay so again before i can show you the solution let's try to create some duplicate entry so i'm just going to execute the insert statement and then let's do a select from the cars table so i have this data let's go back to my solution five and i'm telling solution five is basically using a backup table okay now an alternate way of deleting duplicate data and i think this is a faster way of doing it because generally if you are using a delete statement and if you have to delete let's say thousands of records the delete statement might take a lot of time sometimes when we are bothered with performance and we are working on some development environments we basically do is we create a backup table then we drop the original table then we basically rename the backup table to the original table okay so there is a problem with this approach because if you're working on a production environment you basically cannot drop some existing table which is currently being used by your application right but when you're working on some development environment or for some testing projects this is a method that you can follow okay so let's try to do that so first of all i'm going to create a backup table so i'm just going to say create table cards underscore bkp okay i'll say as and i'll say select start from cards table okay now first of all i want to create this backup table as an empty table i do not want it to have any data so what i can do is i can just tell where 1 is equal to 2 so this will always return 0 records right so this will basically create the backup table with the same structure as this cars table okay so i'll execute this now the backup table is created the next thing is to populate this backup table with the unique data from the cards table so how do i identify unique data from the cards table so i'm just going to say select star from cars now one of the way of identifying the unique record or basically the original record is by using the solution that i provided in the previous step that is i can just use this sub query here so what i'll do is i'll just come here from the cars table where id is in so in this case i'm going to put in and then i'll put this sub query okay so this sub query here is basically grouping the data based on model and brand it's going to fetch the minimum of id which basically is the original records that got inserted into this table right so i'll put this entire thing into a sub query and i'm going to fetch all the data from the cards table from this particular which belongs to this id right so now if i execute this this will basically give me the four records from the table which are the original records so let's say okay if you see the actual cards table there are six records right but because i'm only fetching the ids where which basically is the minimum of id for each of this combination of model and brand i'm only getting the four ids which were originally present without considering the duplicate record right so this will basically give me the unique record from the table then what i need to do is i just need to do an insert of all of this record into my backup table so insert in two cards underscore bkp okay and then this whole select statement so if i execute this the insert four has happened that means four records have been inserted so the next step that what i need to do is now what basically i have done is i had the cards table which has the duplicate records i i created a backup table with the same structure as a card stable then i loaded all the unique records from the cards table into the cart's backup table next thing is i will drop the cards table so drop table cars okay i'll i have dropped the table and then i'll alter the table cars underscore bkp that is cars backup table and i'll rename to the cards table so basically i'm going to rename the backup table into the original table name so if i execute this now the backup table has been renamed as cars and now if i query the data from the cards table you can see that i'm only having the unique records okay so this is one way of removing duplicate records but there is a problem here and this is not the way to remove duplicate records especially if you are working in a production environment because this cards might be a table that is being used by some of your application if you drop a table that is currently being used in your application your application may crash right so this is not a very good approach this can be used when you are internally doing some processing or some testing and you want to delete the data like thousands of data or millions of data very quickly then this is going to do it very quickly okay but an alternate way of doing the same approach would be my solution 6 so let's come back to solution 6. here almost half of the things that i'm going to be doing is the same so what i'll do is i'll just copy all of this here and i'll just paste it here okay because the initial step so i think first of all i do not have a cards backup table here because i already renamed it to cars but i think i do not have duplicate data so first of all let me insert these two duplicate data and then check my card table okay now i have duplicate data now let's go back to solution six so now let's go ahead and create the backup table so i have created the empty backup table now i'm going to insert the unique records into the backup table okay the next step i did in the previous solution was to drop the cardstable but rather than dropping an alternate way is we can just empty the cars table so what i can do is i can just tell truncate table cars so all the data from cars is truncated and now i can insert the data insert into cars from my backup table so select star from cars underscore bkp because if i see my cards backup i have the unique records and now if i execute this you can see that unique records have been moved into my carts that is my original table and then i can drop the backup table so drop table cards underscore bkp and i have dropped this and now if i go and query from my cards table you can see that i have this unique records okay so this is my solution six how you can remove duplicate records from your table okay i hope all of this is clear i hope it is not too fast i am going to be sharing all the solution in my blog now this all these six solutions was for a scenario where the data is duplicated based on some columns and if you see carefully all of these solutions i have used the id column to basically remove the duplicate data okay except for maybe uh i think even here in the solution five and six also i have used the id column but now let's look at the scenario 2 where the id column also may be duplicated in that case how would you remove duplicate records so to explain that i'll open a new tab here i have the same table created but in this case i have basically duplicated the data not just for the model and the brand column but for every column you can see here i have this ionic five belonging to hyundai and you can see even the id is the same the color is the same the make is the same everything is the same and the same thing for tesla model as the id brand model and every other column are all same okay so now all this method that we have used to remove duplicate data will not work because even the id is duplicated if i try to delete the data based on id not just the duplicate record but even the original record also will get deleted right so for this kind of cases i'm going to give you four solution of how you can remove duplicate data okay so the first solution that i'm going to be giving you is something called by using the ct id now the city id is basically something called like an row id okay and this city id is only available in postgresql okay if you're using postgresql then you can use this particular column to remove duplicate data but if you are using oracle then similar to ctid in oracle we have something called as row id okay so row id in oracle but unfortunately as far as i know in mysql and in microsoft sql server we do not have a alternative to these two unique ids okay so what exactly is this i'm going to explain now so let's do a select star from my cars table okay and if i execute this you can see that i'm having six records now in postgresql every time you create a table internally postgresql is going to create a pseudo column which is called as ctid it's basically going to assign a unique row id for every single record in your table okay you don't create it postgresql creates it and internally it will maintain it okay similar to that in oracle we have something called as a row id okay so for example here if i just do ct id and if i execute this even though in my table i did not create ct id it's still showing up here because this is something that internally postgresql has created okay and this is going to have a unique value for every record okay the value here is in the form of a tuple okay so now what we can do is i can basically delete the data from this table using the same approach that i followed in solution one so now instead of rewriting the entire query what i'll do is just to save time this was a solution one where i have deleted duplicate records by using the unique identifier that was id right so i'll just copy this entire query here okay and i'll just try to paste it here and here i'll not use id why can't i use id because even id is duplicated in this case right so here you can see i'm getting max of id as one and four because if i go here for both these records okay so let's say if i execute this you can see that both ionic phi is having the id as 4 right if i try to delete the data based on id both these records will get deleted i don't want that to happen so what i'll do is instead of using id i'll use this ct id okay and here also i'll use ct id now if i execute this you you see the ct id here okay so for example i'll just show you the ct id here uh so i think for this right so you can see the city id is 0 comma 6 and the ct id here is 0 comma 4. the cpid is actually different right i'm going to use that now if i execute this sub query here you can see that the max of ct id for the duplicated records are 5 and 6 right now if i execute this whole statement the duplicated records will get deleted and now if i execute from this particular table you can see that i'm having the unique records so this is solution one when every column is duplicated we can use something called as ct id the query is almost the same that i used when i was using the id column but this will only work in postgresql because ctid is supported in postgresql and in oracle we can use row id right just replace ctid with row id and it will work but in mysql and microsoft sql this approach will not work then what is the other solution so for that i'm going to give you a solution to which basically will work in any rdbms so it does not matter whether it is mysql microsoft sql server oracle postgresql or any other this solution 2 is basically going to work in any rdbms so before i can show you the solution to first of all since i already removed the duplicate data in my solution 1 i need to repopulate the table with some duplicate entries so i'm going to rerun this insert statement and now if i run my select query you can see that the duplicate data has now been populated here now let's come back to my solution 2 and see how i can write a query which is going to remove duplicate data without using ct id and which should probably work in mysql microsoft sql server etc okay the solution is since i do not have any column with unique identifier i'm going to create a temporary unique id column okay so i'll create a temporary unique id column and using that unique id column i'm going to do the delete and after my delete of duplicate record is done then i will drop this temporary column okay so let's try to do that so first of all i'm going to alter my table so alter table cars okay add column and i'll just tell my column name would be something like under let's say row underscore number okay or row num okay it's going to be an integer and i'm going to auto populate this column with unique uh values using the identity function okay so i'm just going to say int generated always as identity okay and this identity column or function is basically supported in every rdbms i think the syntax might be slightly different but it is supported in every other rdbms okay so let me just execute this and you can see the alter table is successful and now if i go and query from my cards table we can see that a new column has been added and this value here is all unique so now once i have this i can remove the duplicate records by using this row number column so again instead of rewriting the query i'll just reuse the query that i already wrote in the previous solution so i'll just paste it here okay and instead of ct id i'm going to be using the new column that i created okay so row number that is row number here and now if okay so before i execute i'll just execute this sub query here you can see it's returning five and six okay the id of the or basically the row number of the duplicated records and now if i run the delete statement you can see two records are deleted and if i go back and query from the cards table you can see only the unique records are present but this new column is still present here now the last step here what we need to do is we need to again alter the table cars and i need to say drop column row underscore num okay and now if i do this you can see my column is dropped and if i execute from cards table now this is exactly how the table was prior to i created this column right so this is an alternate solution and this solution should probably work in any rdbms so if you have duplicate data in every single column this is a pretty good solution that you can use everywhere i hope this was clear now let's go back to my solution three okay and this solution is again by using a backup table this time it's going to be much simpler because every column is duplicated right so before that uh let's repopulate the table with some duplicate records and query the table so you can see i have this duplicate entries now let's go back to my solution i think that is this one creating the solution by creating a backup table so what i'm going to do here is in this step i'm going to create a backup table but when i'm creating the backup table itself i'm going to load it with the unique records so what i mean is i'm just going to say create a table i'll say cards underscore bkp as and here i'll just tell select distinct star from cars okay because every column is duplicated right so if i do a distinct of star i will get the unique records here i don't need to do a sub query with max and etc just a distinct of star is going to return me to unique records because every single column is duplicated right and using this query i can create my table so here not just the table is created but the data with basically with the proper unique record is also created so if i do a select star from cars backup you can see that i have the table with the proper data here and the next step is again pretty simple i just drop the original table drop table cars okay and then i just rename the backup table to the original table so i just tell alter table cars underscore bkp rename to cars okay and that's all now if i query the data from my so let's say if i query the data from my cars table you will see the four unique records okay so this is one of the solution again the problem is that you are dropping the original table if the table was used in your application your application is going to crash an alternative way is my solution for so in solution for again i'm going to be creating a backup table so what i'll do is okay first of all let's repopulate the table with some duplicate data okay and let's look at my query the cards table i have duplicate data and then i'll just basically copy all of this step here so i'll just copy into step four because the solution four is almost similar to solution three so i create a backup table by doing a disk query that is select distinct of cars so if i remove this thing you can see that i have duplicate data six records here but in order to remove the duplicate data i just put a distinct here okay and then by this will basically fetch me all the unique records and from this query i'm going to create my backup table uh using this particular syntax so my backup table is created with the proper data the next step is instead of dropping the original table i'll just truncate the original table truncate table cars okay and then i'll just populate my original table from the backup table so insert into cars select start from cars underscore bkp if i execute this card stable is populated and the last step is i just need to drop table cards underscore bkp okay so that's all and now if i execute from my cards table if i query the data from my cards table you can see i have the unique records okay so that's all these were the 10 different solutions that i wanted to show you i hope this is clear and these are all pretty simple queries that is why i have gone very fast uh if you like this video please give me a like and leave your feedback in the comments below if you have any queries that you would like me to solve please share them in my email i will be adding a lot more videos into this playlist so anyone who wants to attend an interview or wants to practice sql this would be the three playlist that you can always refer to okay so thank you very much for watching and see you soon in the next one bye

Channel: techTFQ

Views: 200,022

Keywords: sql, sql queries, sql tutorial, sql queries tutorial, remove duplicate date, deuplicate data, delete duplicate data, duplicate data in sql, remove duplicate data in sql, multiple ways to remove duplicate data in sql, techtfq sql, techtfq, delete duplicate rows in sql, delete duplicate records in sql server, delete duplicate rows, how to delete duplicate records in sql, remove duplicates from a table in sql

Id: h48xzQR3wNQ

Length: 37min 31sec (2251 seconds)

Published: Fri Sep 02 2022