Hi Sir, post we run these query still showing duplicate records in table------ delete from frds where frd_id not in ( select max(frd_id) from frds group by frd_name,dob,salary,dept_id);
good day . i have an issue with my leads platform . when i enter a lead it says it already exists . i wa looking into this issue and found that it maybe an issue with a DUPLICATE KEY . does anyone have any idea .
Sir I have a doubt, Suppose with the same table if there is no ID column but there are some other column as well say (deptno,deptname,loc, etc)which have duplicate records and the table does not have primary key column defined in this case how should we delete duplicate records where there are more no of columns??
Thanks for the video. I am trying to use CTE and ROW_NUMBER(), however I got this error: The target table tableName_CTE of the DELETE is not updatable. Do you know how to resolve the error?
Looks like delete from a CTE on MySQL 8+ is not working. It works good in Sql Server database. You can go for another approach for deleting duplicate records in mySQL database.
Sir using first method if i have different id name and rest of the records are same then it will not fetch as a duplicate values, So your first method is only for if the duplicate values are same if anything row number value is different it will not
Video is clear...open this video from TH-cam and go to settings and then click on Quality and select 1080pixel quality. Your video clarity will be equivalent to HD video
Very good explanation, very clear and cleaver way.... Thank you it helped me a lot
You are welcome! Thank you for such a nice feedback !
What if we use distinct function? Our output would be the same or not?
Good stuff. I like the way you make easy. Brief and straight to the point. Keep posting more videos
Thanks a lot Tony ! Please subscribe my channel so that you will get all my future videos updates
thank you! been working on the same problem for a while now, and I have finally solved it because of this video.
You are welcome! I am glad that my video helped you.
it's showing "ERROR 1288: The target table duplicate_CTE of the UPDATE is not updatable" in MYSQL. plz help me with this
I am facing this same problem
Hi Sir, post we run these query still showing duplicate records in table------ delete from frds where frd_id not in (
select max(frd_id) from frds group by frd_name,dob,salary,dept_id);
If this query will run in mysqlworkbench 8.0 in Mac laptop because its not running in my laptop..please help
I think MySQL 8.0 supports these SQL queries
Thank you Sir. I have a question. What if the column names are many, will we start typing all of them in
Yes, you will have to type all column names.
good day . i have an issue with my leads platform . when i enter a lead it says it already exists . i wa looking into this issue and found that it maybe an issue with a DUPLICATE KEY . does anyone have any idea .
sir, which platform are you using for this.
i am using CLI its very difficult there to execute big Query.
I am using SQL Server.
I Don't understand, why did you use Duplicate_CTE . Could you please explain.
Same
What if the unique id is not integer but a unique identifier like Guid? You can’t use max there. How will you do it?
Thanks for writing Ken Singh ! Max function works on alphanumeric values as well . I think it will work on Guid as well.
Nicely explained by you sir
Very helpful video
Thank you sir
Thanks Rajesh ! I hope you will like my other videos as well.
Sir I have a doubt,
Suppose with the same table if there is no ID column but there are some other column as well say (deptno,deptname,loc, etc)which have duplicate records and the table does not have primary key column defined in this case how should we delete duplicate records where there are more no of columns??
Hi Kumbhar...In that scenario, you can delete duplicate data by the way i have explained using CTE and Row_Number(). I hope this will help.
@@ItJunction4all sir there also you have used order by id , so how to tackle that one
@@ItJunction4all SIR it is not taking Row_Number() as key word what should i do?
@@SanketBandi Which database are you using ?
Thanks for the video. I am trying to use CTE and ROW_NUMBER(), however I got this error: The target table tableName_CTE of the DELETE is not updatable. Do you know how to resolve the error?
Thank you Sharmine for writing ! Could you please tell me which database you are using ?
@@ItJunction4all I am using mySQL workbench
Looks like delete from a CTE on MySQL 8+ is not working. It works good in Sql Server database. You can go for another approach for deleting duplicate records in mySQL database.
@@ItJunction4all I also tried the first DELETE method, the problem is it took a long time and will only display timeout
@@Sharmine_ Ohh..I see. How many duplicates you are trying to delete from your table ?
Sir using first method if i have different id name and rest of the records are same then it will not fetch as a duplicate values, So your first method is only for if the duplicate values are same if anything row number value is different it will not
Thanks for writing Prashant ! First query will work if you have unique id for each row.
Let me know if this clarifies your doubt .
@@ItJunction4all got it sir.. Thankyou 😇
@@prashantsingh1892 Welcome Prashant !
could you write a case statement for duplicate rows and delete duplicates that way?
I don't think it is possible to delete duplicate records using case statement
Hi,
i am executing with clause query in Oracle PLSQL developer and i am getting error missing select keyword.
Please help me.
I'm facing the same issue. It seems it is application dependent and not working to delete cte records
Thank you so much! Very loligicall explained.
You're very welcome!
This was really helpful.
You are welcome 😍😍
Thanks for your help. 2nd option Helped
You're welcome Aqib !
Nice explanation.. Thank You...
You are welcome Suman !
Love ur explanations
Thanks a lot 😊
Your method is not working for me bro is shows error: releation "duplicate_cte does not exist
Line 6: Delete from Duplicate_CTE where Dupont >1
Which database are you working ?
Postgres bro I was new pls guide me
great explaination
Thanks a lot Rohan Devaki ! 😍 😍
what is CTE?
CTE is Common Table Expression
fabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
finally got a solution!!!!!!!
thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You are welcome ! I hope you will like other videos of my channel as well !
Sir how about in windows 10?
Sir before making video please maintain visible screen size it's hardly visible to me may be it's only my problem or for other also don't know...
Ok Sure. Thanks !
Sir great information but video is not proper visible screen size very small please can you make this same video one more time and upload
this is really helpful
Thank you !
Are you continuing these questions?
Yes I will continue adding questions as and when time permits me.
Thanks Alot
You're welcome Lokesh ! I hope you are liking my other SQL videos as well. Let me know your feedback ?
NOT WRITTEN QUERY IN DESCRIPTION PLEASE ADD THE QUERY
Vishnu..All queries are there in video.
Thanks a lot
Most welcome Sayandeep !
What is duplicate cte
CTE is a Common Table Expression. With the help of CTE , duplicate record was deleted
thank you
You're welcome Hannah Getachew !
Thanks
8:21 using cte
Thanks Manish Sharma !
Video not clear
Video is clear...open this video from TH-cam and go to settings and then click on Quality and select 1080pixel quality. Your video clarity will be equivalent to HD video
Thank you
You're welcome Rizwan !