DELETE DUPLICATE ROWS FROM A TABLE IN SQL || 4 ways

today's video will understand how to remove duplicate rows from a table so there are four ways by which we can do so first is using a temporary table second is missing Auto ID thought is using row number fourth is using CTE let's understand each one of them one by one the first way is by using a temporary table so the what are we going to do is first we'll select distinct star into new table from all tables so what is this step doing is it will select all the distinct items and put it in a new table now a second step is delete stuff from all table so this will delete all the items from the old image you can either do you can also do truncate away oh okay whichever's what's best so we delete delete all the items from the old table and third step is will insert into over table select star from new table so we must select all the items from the new table and what is our new table have all the resisting items and be inserted into a into our old table which is empty at this very moment so so so our old table will now have all the distinct items and the fourth step would be drop table new table so we will drop the new table this is how by using temporary table we can remove all the duplicate items okay second way is by using Auto ideas so what we gonna do is we have a table employee which has employee ID a name employing him it has some duplicate items like five zero zero one is repeated thrice so this is a duplicate item so what are we gonna do here is first of all this first of all we are going to add an auto ID in this table okay Auto ID is something like this out auto it is like this one two three four five they are using it here because the employee IDs are something like five zero zero one five zero zero two five zero zero three but we want all of them to be something like one two three four five and each one should each each row should have one unique identifier hence we use auto ID so this is how we alter the table alter table employee alter this table employ add Auto ID Auto ID is just a name given to this field okay so add Auto ID and it's its data type would be end and identity 1 comma 1 what does identity 1 comma 1 do is or what are the parameters of identity when a serial one is increment seed is basically from which you want to start okay so I was started from one because I'm starting from one and increment one means I will increment it by 1 every single time so one two three four five one by one and this is how I will add a auto ID in this table okay now our next step is to delete star from employee where Auto ID not in select main Auto ID from employee group by imp ID implements the first of all the inner why this sub query will be executed so what is the inner query select min Auto ID from employee group by impeding as we already know min is a aggregate function whenever there are aggregates functions we have to use group by so it's going to group them by importing em PI D so all the file 0 ones will be together Fiser tools together 5-0 threes will be together okay and now it will select min from each one of them so basically this inner inner query will return the minimum of all all the Empire ease so it will return one it will return three and it will return to it will not return four or five because four and fire or not minimum they are not minimum I hope this is clear so the inner query will return will return one two and three okay because it is selecting the minimum Auto ID minimum from these well in the groups okay okay the first group will be five zero zero one five zero zero one five these three will be in one group and the minimum of these three group is one so one will be returned five zero zero two is only one 101 times return so it will return three five zero zero three will return - okay so this is what this inner query will return what will it return one two and three now what is the outer query delete star from employee where auto ID not in what is there no query one two three so basically it will delete all the items from the stable which are not one two three so except one two three it will return all the other rows so it will it it will delete fourth one fifth one because delete isn't remember so basically only the first three will be returned or and the rest two will be deleted and that is our motive we have to remove the duplicate other than that we dead using Auto ID so I think this was clear now the third way is by using row number now let's see the delete from this is an inner query and there is a condition where condition okay let's see the inner query force so the inner query says select star comma row number or partition by ID order by ideas aren't from might Evan let's understand what row number exactly does over here okay so that is the row number roll number is also something like one two three four it's giving unique identifiers but what is what is the written I had of a true number over partition by ID now what this does is it will partition it by what ID so it will create partitions now initially my table was something like this it had employee employee ID and employee name by zero zero one five zero zero two five zero three five zero zero one five zero zero two being repeated thrice ok so this is what the table initially looked like now when I write something like a row number over or partition by ID now I will partition them by IDs so all the IDS will be written together so C five zero zero one five zero two sorry Phi 0 0 1 5 0 mana reading together the five zero tools are written together Phi 0 3 is written together so this is basically creating partitions and now I've written row number over partition so every time a new partition stars it will start with a new rule number so see the five zero one has two row numbers one and two five zero zero two will I can start with 1 1 2 3 and 5 0 0 3 will start with 1 this is basically what row number does so row number over partition by ID order by ID no waters order by ID means basically it will order them by this ID it's supposed to be imp underscore ID but I wrote an okay so it will order them by ID so 5 0 0 1 5 0 2 in order it isn't okay so this entire thing row number this entire thing as are in this entire underline the one I would underline with yellow is 1 and entire thing so this entire row number will be will be will be called RN because I've written this is this as RN so this is alright okay so basically this this inner query is returning what this table so let's understand again select star comma number over partition by ID order by ideas aren't from my table whatever your table name was okay this is what it will return now what is waters are out required delete okay delete everything from this inner query where our n is greater than 1 so wherever our an RN is one row number wherever our n is greater than 1 so this 2 2 3 all of these will be deleted and hence will only be left with five zero one five zero zero one five zero zero five zero two three early ones and hence we will remove all the duplicate items now the fourth way is by using CT what is it a common table expression and explain it in a very short name so what is CTE it is used as a temporal result set and it lasts only for the duration of work where you can just understand it as of now as a temporary table which will only last the duration of the query so this is very similar to the above query so your we write with CT CT is just a name given to CTU okay zip with CT as so this the this temporary table is this inner query select row number over partition by ID order by ID as I earn from my table this is the same thing which was written over on the same thing is now just being used with CT so this will again return this the city is basically this table again and the next thing is delete from city where our n is greater than months so wherever our n is greater than 1 it will be deleted this is very similar to the above query I hope this was clear thank you for watching

Channel: Crack Concepts

Views: 93,600

Rating: 4.9057899 out of 5

Keywords: Delete duplicates from a table in SQL, Remove duplicates from a table in SQL, Delete duplicate rows in sql, Delete duplicate records in sql, Remove duplicate records in sql, Sql interview questions, Delete duplicate records in sql server

Id: rh626r-4iJU

Length: 9min 15sec (555 seconds)

Published: Tue Jul 02 2019