+aravind kumar eruventy Thanks a bunch for taking your valuable time to give feedback. Means a lot. Glad you found the videos useful. Dot Net & SQL Server videos for aspiring software developers th-cam.com/users/kudvenkatplaylists?view=1&sort=dd If you need videos for offline viewing, you can order them using the link below www.pragimtech.com/Order.aspx Code Samples, Text Version of the videos & PPTS on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our channel th-cam.com/video/y780MwhY70s/w-d-xo.html Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below th-cam.com/users/kudvenkat Please click the THUMBS UP button below the video, if you think you liked them Thank you for sharing these links with your friends Best Venkat
Hi Venkat, You are rock star for beginners to learn SQL Without basics there is nothing in every course. With your course and videos about SQL i got placed in Deloitte. In every interview, interviewers never go for advanced level without asking basic level questions. If we are very strong in Basics you can manage and convenience the interviewer with basic knowledge that will create great impact. Your videos are reference for any SQL interview and for every concept. Thank you so much for your help.
WOW thank you so much for this video!!! I always knew how to select on recordsets with duplicates using a distinct statement but had never thought about using a CTE to delete the duiplicates. You'r e genius!! :-)
Excellent solution to delete the duplicate data using partition function. You are awesome dear, not only this one but all the other videos specially the SQL are fabulous and very much informative and very easy to understand. We learn a lot from your video series. Be blessed and thanks alot.
Revisiting to refresh my memory. I enjoyed watching this video and recommend others. Thanks for educating the community and appreciate all your efforts
Does deleting the row from CTE EmployeeCTE also deletes it from the underlying Employees table? Did I miss anything here? I thought CTEs are just like views to make joins
Thats a good question, now when working with CTEs you have to understand they are not normal tables but just 'table expressions', what that means is CTE is created over an already existing normal table, in other words , the CTE's are always working over underlying table. so any modification rows of CTEs are actually performed on underlying tables.
also , another question can be , what if the CTE joins two tables and user decides to delete row/rows from that CTE, in this case SQL server will give an error.
Seems like this query isn't working on PostgreSQL DB. Will you please check and confirm the same? Just for reference, I have tried running two different versions to achieve this: ****** Version 1 ****** :-- WITH tempEMP as ( select row_number() over(order by id, firstname, lastname, gender, salary desc) as rn, employees.* from employees ) delete from tempEMP where rn not in (select max(rn) from tempEMP group by id, firstname, lastname, gender, salary) ******* Version 2 ****** :-- with tempEMP as ( select row_number() over (partition by id order by id ) as rn from employees ) DELETE from tempEMP where rn > 1; Note: I am able to run the SELECT query but in the case of the DELETE query, I am getting a common error from both of the above queries. Here is my error: SQL Error [42P01]: ERROR: relation "tempemp" does not exist¶ Position: 383 PS: Position in error log changes with the query change.
thanks a lot for this video, but I have a question. you've deleted rows based on duplicated IDs, in real environment, duplicated rows suppose to be considered on multi columns.. am I right?
But cte is a just a temporary result so when we delete rows from cte, why is it deleting rows in main employees table. Can u tell me why cte is effecting main employees table. I don't get this logic. Please explain
At 1:50, I'm trying to copy the query for my example, but I get an error on the "With".... I'm using phpMyAdmin through WAMP. Has anyone else experienced this? Thank you for your time.
@Csharp-video-tutorialsBlogspot Hello Venkat, this query is not working in MySQL and giving an error the target table is not updatable. Can you please assist on this ?
that is very good solution sir thank you and i have one doubt if id or primary key is not there means how to how to achieve this please suggest me. thank you in advance
Great video, thanks!! Question: in the video, you are assuming that you know that there are duplicate rows in the table.. How can we alter the query to check first for duplicate rows? Meaning, based on your example, what if we had rows with same value for ID but different values for other columns.. In that case they would not be duplicates.. How to handle such scenario? Thanks again
Very good question. As a developer my next question will be what are the conditions then for considering the rows to be duplicate, based on which the query logic depends. Hope this makes sense to you.
you could use this query to check for duplicate rows (you'll need to alter the column name to suit your table): SELECT UserName, COUNT(UserName) AS NumOccurrences FROM MyTable GROUP BY UserName HAVING ( COUNT(UserName) > 1 )
kudvenkat By using physical location(%%PHYSLOC%%) can we find which row inserted first and which one is new up to now in a table ...,so that based can we delete latest or else old records based on requirement??? is there any possibility like that........,
@@ksreddyk2776 yes , DELETE FROM PhysLocTest.SeveralRows WHERE PhysLocTest.SeveralRows.%%physloc%% NOT IN (SELECT MIN(b.%%physloc%%) FROM PhysLocTest.SeveralRows b GROUP BY b.Category);
I accidentally deleted some columns and I’m having a hard time adding them back to my table. Do you know of a sql statement that I can use to add the columns back to my table?
Hi Venkat , I have a question ,I want to keep a track of the duplicate records I am deleting and I want to store that in a table before delete is that possible using CTE ,IF not then what should be the best way to do it in SQL ? Thanks
Thankyou for the explanation I understood it but its not working in PostGre SQL, kindly help me to know what difference we can make to run it in PostGre ?
How can I delete rows that are duplicate based on the concatenation for three columns? Meaning for example the outcome of the columns is the same in another row
For every record there is row number been assigned In delete statement before delete the record it check for the value of row number if it is greater than 2 then that record will be deleted. In that case record which contains the row number value as 1 those records only remain in a table
Hi Mr. Venkat, I find your videos very helpful for SQL learning. I have a doubt in this video. You deleted rows from the CTE, then how were they deleted from original Employees table.
Sir what if we didn't assign any row num column to it and how to delete the repeated values (ex:- their is a table called empolyee. Here the empid,emp name,salary are their as the columns but we have same emp names but they contain different empid and salary but we have to delete one of the repeated emp name with out any condition ) is it possible sir because in one interview this question had been asked by an interviewer.
Hi Sir , why is Order_BY ID is needed here? From the groups of ID, we can directly delete all except one and in that group all IDs are same so why would we Sort it? Can you please explain , in case interviewer asks me this?
thanks alot for these all efforts , i have question regarding find duplicate values in most cases the id , is PK and its sequential number which not allow duplicate, so you find duplicate in your example if the name, gender and salary are same but they have different ID ? many thanks
Grate it works for me, Saved lot of time
Thank you very much....
+aravind kumar eruventy Thanks a bunch for taking your valuable time to give feedback. Means a lot. Glad you found the videos useful.
Dot Net & SQL Server videos for aspiring software developers
th-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need videos for offline viewing, you can order them using the link below
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use our channel
th-cam.com/video/y780MwhY70s/w-d-xo.html
Want to receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
th-cam.com/users/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing these links with your friends
Best
Venkat
Yes your 2nd and 3rd records also deleted.keep it up.
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
I'm data engineer today because of this guy. ❣️
Can never thank you enough.
That is my goal!
hi ranajan
6 years on and still the clearest solution to the age old problem of deleting duplicate rows in a heap in SSMS. Awesome!!!
Kudvankat, You are giving a Public Service. I can't thank you enough for your Clear and detailed tutorial. God bless you, Sir.
Hi Venkat,
You are rock star for beginners to learn SQL
Without basics there is nothing in every course.
With your course and videos about SQL i got placed in Deloitte.
In every interview, interviewers never go for advanced level without asking basic level questions. If we are very strong in Basics you can manage and convenience the interviewer with basic knowledge that will create great impact.
Your videos are reference for any SQL interview and for every concept.
Thank you so much for your help.
Hi Rama, for 5 years till now they are asking this same question and due this i got rejected.
@@gamemaniax9935y you rejected
Mr. Kudvenkat, you are an excellent instructor! The code was very well explained and the example was very helpful. Thanks!
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
i love you Kudvenkat .. developers will give you blessings always.
Thank you, this helped a lot! I needed to delete 53 duplicate data lines and this came right on time!
WOW thank you so much for this video!!! I always knew how to select on recordsets with duplicates using a distinct statement but had never thought about using a CTE to delete the duiplicates. You'r e genius!! :-)
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
This is the best optimal solution i have seen !
Thank You !
This is the solution I was looking for a very long time!
Thanks a lot! Great work!
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
you are excellent instructor! example was very helpful. Thanks a lot!
Really! you are a good teacher. Easy to understand all your videos. Thanks a lot!
Excellent solution to delete the duplicate data using partition function. You are awesome dear, not only this one but all the other videos specially the SQL are fabulous and very much informative and very easy to understand. We learn a lot from your video series.
Be blessed and thanks alot.
Revisiting to refresh my memory.
I enjoyed watching this video and recommend others.
Thanks for educating the community and appreciate all your efforts
SAVED MY LIFE! THANK YOU! GREAT VIDEO!!!!
Thankyou sir , I learn CTE also … your way of teaching is great.
Correct guy, you are. Thanks. I'm new to the journey. You're 👍👍👍
This worked for me... Kudos brother :)
This is the easiest and most efficient answer I've found! Thanks for this!!
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
Does deleting the row from CTE EmployeeCTE also deletes it from the underlying Employees table? Did I miss anything here? I thought CTEs are just like views to make joins
great effort in all video.....very clear explanation
Try this ...
th-cam.com/video/yC8pZabO5Sg/w-d-xo.html
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
u r hunting the quires....straighht forward and indirectly ....great
Great man
your all the videos are Awesome
Hats off kudvenkat
This is a very interesting video, keep up good job.
This guy's voice is so soothing, besides being intellectual ;). Is he married? lol! Jokes apart, thanks for these exceptional videos brother!
Just one thing I don't understand:
You're deleting from the EmployeeCTE, but how come it delete the row in the Employee table?
same doubt to me also....have u got answer for this kindly share......
William Xu CTE is an inbuilt function. Common Table Expression. It doesn't affect table name.
Thats a good question,
now when working with CTEs you have to understand they are not normal tables but just 'table expressions', what that means is CTE is created over an already existing normal table, in other words , the CTE's are always working over underlying table.
so any modification rows of CTEs are actually performed on underlying tables.
also , another question can be , what if the CTE joins two tables and user decides to delete row/rows from that CTE, in this case SQL server will give an error.
Try this ...
th-cam.com/video/yC8pZabO5Sg/w-d-xo.html
Finally able to do it 😊👍 Thank you so much !!!
Awesome. Crystal clear if someone knows window functions
Thank you Venkut Sir, I learn a lot from your videos
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
Thanks for the video. You provided exactly what I needed to get started with my query.
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
Seems like this query isn't working on PostgreSQL DB. Will you please check and confirm the same?
Just for reference, I have tried running two different versions to achieve this:
****** Version 1 ****** :--
WITH tempEMP as (
select row_number() over(order by id, firstname, lastname, gender, salary desc) as rn, employees.* from employees
)
delete from tempEMP where rn not in (select max(rn) from tempEMP group by id, firstname, lastname, gender, salary)
******* Version 2 ****** :--
with tempEMP as
(
select row_number() over (partition by id order by id ) as rn from employees
)
DELETE from tempEMP where rn > 1;
Note: I am able to run the SELECT query but in the case of the DELETE query, I am getting a common error from both of the above queries. Here is my error:
SQL Error [42P01]: ERROR: relation "tempemp" does not exist¶ Position: 383
PS: Position in error log changes with the query change.
What a clear explanation . I solved my problem. Thank you !
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
I love you sir, you are my hero.
thanks a lot for this video, but I have a question. you've deleted rows based on duplicated IDs, in real environment, duplicated rows suppose to be considered on multi columns.. am I right?
All part very nice 👍
But cte is a just a temporary result so when we delete rows from cte, why is it deleting rows in main employees table. Can u tell me why cte is effecting main employees table. I don't get this logic. Please explain
If cte depends on one base table it affects base table, check kudvenkat's updatable CTE lecture
As usual awesome , thank you sir!!
awesome,thanks a lot for your explanation.Can u post the video for lock in sql server.
Thank you so much!! this saved me a lot of time
Many thanks Venkat,you are a genius! Bless you.Cheers
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
this is very useful video and understandable.... Thanku so much
great job, nice videos and good explanation........thank u
Not all heroes wear capes! thanks buddy
Tq sir simply superb
very useful example , have a great day
At 1:50, I'm trying to copy the query for my example, but I get an error on the "With".... I'm using phpMyAdmin through WAMP. Has anyone else experienced this? Thank you for your time.
what if there is no id column on which partition is getting done ? so are we going to do the partition on name column or else ?
@Csharp-video-tutorialsBlogspot Hello Venkat, this query is not working in MySQL and giving an error the target table is not updatable. Can you please assist on this ?
Very helpful tutorial! Thank you +kudvenkat!
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
that is very good solution sir thank you and i have one doubt if id or primary key is not there means how to how to achieve this please suggest me. thank you in advance
this is class, thank you
I'm a beginner 4 months into learning SQL. Isn't this method the same as simply using SELECT DISTINCT ID?
Great video, thanks!!
Question: in the video, you are assuming that you know that there are duplicate rows in the table.. How can we alter the query to check first for duplicate rows? Meaning, based on your example, what if we had rows with same value for ID but different values for other columns.. In that case they would not be duplicates.. How to handle such scenario? Thanks again
Very good question. As a developer my next question will be what are the conditions then for considering the rows to be duplicate, based on which the query logic depends. Hope this makes sense to you.
you could use this query to check for duplicate rows (you'll need to alter the column name to suit your table):
SELECT UserName,
COUNT(UserName) AS NumOccurrences
FROM MyTable
GROUP BY UserName
HAVING ( COUNT(UserName) > 1 )
kudvenkat By using physical location(%%PHYSLOC%%) can we find which row inserted first and which one is new up to now in a table ...,so that based can we delete latest or else old records based on requirement??? is there any possibility like that........,
Hi bro
@@ksreddyk2776 yes , DELETE
FROM PhysLocTest.SeveralRows
WHERE PhysLocTest.SeveralRows.%%physloc%%
NOT IN (SELECT MIN(b.%%physloc%%)
FROM PhysLocTest.SeveralRows b
GROUP BY b.Category);
I accidentally deleted some columns and I’m having a hard time adding them back to my table. Do you know of a sql statement that I can use to add the columns back to my table?
Hi Venkat , I have a question ,I want to keep a track of the duplicate records I am deleting and I want to store that in a table before delete is that possible using CTE ,IF not then what should be the best way to do it in SQL ? Thanks
can we use rowid to delete duplicate?
As i run this command is shows erros like-Recursive common table expression 'Tbl_DeleteMulRow' does not contain a top-level UNION ALL operator
Thankyou for the explanation I understood it but its not working in PostGre SQL, kindly help me to know what difference we can make to run it in PostGre ?
How can I delete rows that are duplicate based on the concatenation for three columns? Meaning for example the outcome of the columns is the same in another row
That really helped me! Thx a lot.
good job ,,keep up ...
So that’s how it’s done!
2:42 rownumber is not showing while typing as show here what to do.
can someone please explain why when row_number>1, row numbers with 2 and 3 are not getting deleted?
For every record there is row number been assigned
In delete statement before delete the record it check for the value of row number if it is greater than 2 then that record will be deleted.
In that case record which contains the row number value as 1 those records only remain in a table
Hey ,
Delete query is not running , ERROR is invalid object name
Hi sir ,
What if we have different ID’s for each employee?
Love U Sir You are Great, Love From Pakistan
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
super helpful. thank you
AWESOME Guruji
hi
great videos ....can you please share MSBI videos please....
Can't we use it in Snowflake??
Great tutorial.
how is it different from using DISTINCT keyword?
Brilliant Sir :-) kudos
with clause Not recognized MySQL database , What is the alternative?
Very well explained. Thank you!
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
well I wanted to insert records where rownumber > 1 into another table how do i handle that? please help me?
Hi Mr. Venkat,
I find your videos very helpful for SQL learning.
I have a doubt in this video. You deleted rows from the CTE, then how were they deleted from original Employees table.
I got the same question at first
but when I tried to execute the query in SSMS
the query actually delete rows from employees table
nice trick to delete duplicate data
can you please write a code for the mysql, i have tried a lot on TH-cam but nothing helpful
Simple and working fine.
CTE will not work when the data volume is huge.In that case,how do we write the code?
Thank You very much for your response in uploading this video sir.........
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
It works for me, Thanks!
Does it work on Oracle? Oracle CTE is only supporting SELECT statement.
It throws error when I attempt to delete rows from CTE.
I also tried the same in Oracle for delete it throughs error., please let know if you got any solution,
Sir what if we didn't assign any row num column to it and how to delete the repeated values (ex:- their is a table called empolyee. Here the empid,emp name,salary are their as the columns but we have same emp names but they contain different empid and salary but we have to delete one of the repeated emp name with out any condition ) is it possible sir because in one interview this question had been asked by an interviewer.
sir, can we delete duplicate rows without using cte?
Yes you can by using group by and having clause
I tried the same code but why it is showing " Unrecognized statement type ( near WITH). Please help!
Hi Sir , why is Order_BY ID is needed here? From the groups of ID, we can directly delete all except one and in that group all IDs are same so why would we Sort it? Can you please explain , in case interviewer asks me this?
Thank u so much 😊
You are awesome.
Can we use the DISTINCT function with SELECT statement?
thanks alot for these all efforts , i have question regarding find duplicate values
in most cases the id , is PK and its sequential number which not allow duplicate, so you find duplicate in your example if the name, gender and salary are same but they have different ID ?
many thanks
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
Wow thank you so much
Great work thank you..
Awesome. Thank you
Can you explain differences between union and union all, coalesce and isnull, how to optimise sql queries.
You are great!!! Thanks a lot, I save a lot of time of my life, thanks to you..... :)
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
why it is not working in mysql database