Master the art of with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch. www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english Course contains 2 portfolio projects and a bonus session on Tableau. 100+ interview problems to crack any SQL INTERVIEW.
Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table?
create table department( dept_id int, dept_name varchar(10) ); insert into department values(100,'Analytics'); insert into department values(300,'IT');
For the First time in 10 years, I am feeling confident with using SQL, only after watching your series of videos. Earlier I used Python to do data preprocessing after doing Vanilla Select * from Table Statement, which was not a recommended approach for memory efficiency.
Bang on ! Started product based companies interview prepration for Sr. DE role ( Google/Microsoft/Ubder/Amazon/Expedia / AirBnB) etc...and your content is crisp and clear. Thanks
Hi Ankit ji, I seen some of the videos and those are very interesting, and useful. I request you to create a video on SQL Index in details with examples. 1. Before creating index how the data loding in page level. And after creating index what kind of table ( page level) structure will be. 2. What will be structure while create only cluster and only noncluster and both. Mean every one can understand beginners to exp students. Waiting for your new video.
I solved it using self join: as we are performing row level aggregation, I guess this is a good case for that: SELECT DISTINCT(e1.emp_id), e2.val as salary, e3.val as bonus, e4.val as hike_percent
FROM emp_compensation e1 JOIN emp_compensation e2 ON e1.emp_id = e2.emp_id AND e2.salary_component_type = 'salary' JOIN emp_compensation e3 ON e1.emp_id = e3.emp_id AND e3.salary_component_type = 'bonus' JOIN emp_compensation e4 ON e1.emp_id = e4.emp_id AND e4.salary_component_type = 'hike_percent'; Approach 2: with stats as ( SELECT emp_id, CASE WHEN salary_component_type = 'salary' THEN val END as salary, CASE WHEN salary_component_type = 'bonus' THEN val END as bonus, CASE WHEN salary_component_type = 'hike_percent' THEN val END as hike_percent FROM emp_compensation) SELECT emp_id, SUM(salary) as salary, SUM(bonus) as bonus, SUM(hike_percent) as hike_percent FROM stats GROUP BY emp_id
@Md Jahidul Islam Razan - Approach 1 - 2.44 seconds vs Approach 2 - 1.31 seconds , i have tested in databricks community edition ,since underlying database is usually mysql, postgressql i think, CTE is more quicker to output both approaches are good !
I just discovered you, subscribed you and started watching your videos. I was asked a question in an interview which is similar to q 6 but a li'l twist in it. Q - get 2nd highest salary from each dept and if a dept does not have a second salary then first salary should appear in result.
@@rohitas7586 Query for getting 2nd highest if not present then 1st highest- with cte as ( select * from ( select *, dense_rank() over(partition by department_id order by salary desc) as rnk from emp ) a ) select distinct emp.department_id, coalesce(cte.emp_id, first.emp_id), coalesce(cte.emp_name, first.emp_name), coalesce(cte.manager_id, first.manager_id), coalesce(cte.salary, first.salary) as salary from emp left join cte on emp.department_id = cte.department_id and cte.rnk=2 left join cte as first on emp. department_id = first. department_id and first.rnk=1 order by emp.department_id ; DDL for emp table and insert query- create table emp( emp_id int, emp_name varchar(255), department_id varchar(255), salary int, manager_id int); insert into emp values(2,'Mohit', '100', 15000, 5), (3, 'Vikas','100',10000,4), (1,'Ankit','100',10000,4), (4,'Rohit','100',5000,2), (5,'Mudit','200',12000,6), (6,'Agam','200',12000,2), (7,'Sanjay','200',9000,2), (8,'Ashish','200',5000,2), (1,'Saurabh','900',12000,2);
insert into department values(100,'Analytics'); insert into department values(300,'IT'); create table orders( customer_name char(10), order_date date, order_amount int, customer_gender char(6) );
insert into orders values('Shilpa','2020-01-01',10000,'Male'); insert into orders values('Rahul','2020-01-02',12000,'Female'); insert into orders values('Shilpa','2020-01-02',12000,'Male'); insert into orders values('Rohit','2020-01-03',15000,'Female'); insert into orders values('Shilpa','2020-01-03',14000,'Male');
keep doing more videos like it. It really helped me in my job interview today. I am able to answer the question of how to remove duplicates from a table because of your videos.
Amazing video, tomorrow I have an interview, I was clueless how to start and where to start, now I know what to do for tomorrow :)) Thanks for sharing :)
Hi Ankit, first of all this is really a great initiative that you are sharing all this knowledge and explaining the complex queries in a very easy way. And because of this I am really glad to tell you that your channel has majorly helped me to crack one of the SQL interview. Thank you so much for such a great effort and we all wish to see some more complex queries and concepts in coming future, once again thank you so much. Wish you a great health!
You just earned a subscribe from me bro. I haven't used SQL in years, but due to a career change I'm planning to have soon, I'll need to learn more about SQL in-depth alongside bettering my skillset in C++ programming.
Sir by looking at this video it seems SQL is very easy but actually it's not so easy sir, in interview same questions I faced nervous and end up giving wrong query.
but CTE doesn't support delete operations right...I tried it but got below error "The target table cte of the DELETE is not updatable" with cte as (select *, row_number() over (partition by md5(concat_ws(",",emp_id,emp_name,emp_age))) as dup_row_id from employee) delete from cte where dup_row_id>1;
I guess we can use subquery table as a normal table to display information using alias and where clause , but when it comes to comparison it needs to return only 1 value :) !!
Hi Sir, Big thank to you i learn lot of things in sql after watching your video . I want to add to solution from my side please let me know this will fine or not? 1) with cte as ( select *,row_number() over(partition by emp_id order by time desc) as rnk from hospital) select count(*) from cte where rnk =1 and action ="in"; 2) select count(*) as total_person from hospital h where time =(Select max(time) from hospital h1 where h1.emp_id = h.emp_id) and action ="in"; Waiting for your response. Again big thank to you.
In the first question where we wanna find out the duplicates, we knew that the duplicates would be as per emp_id. How about if we wanna check if the entire row is unique or not rather than checking any particular column?
Hey Ankit! I was thinking about that delete duplicate question. U r deleting the duplicate data from cte which is a temporary result set , right? It is working fine but how it's deleting from original table? MySQL doesn't support this from 8+ versions I guess..we have to use that cte query like a subquery in mysql. But in ssis it is working. Even in leetcode ! But I think it is always better to reference the original table rather than querying from cte i.e a temporary result set. Selection is fine with cte but dml operation! Let me know about your thoughts, I would love to hear!
In 2nd Question of removing duplicates: We are deleting values from the CTE table which is a TEMP table. Then how it is deleting values from the MAIN table?
with CTE as ( select * ,row_number() over(partition by emp_id order by emp_id) as rnk from emp order by emp_id asc ) delete from CTE where rnk > 1 From CTE we can't delete records?
with dup as ( select ROW_NUMBER() over(partition by emp_id order by emp_id desc) as Find_duplicate from emp) delete from emp where emp_id in ( select * from dup where Find_duplicate > 1);
Questions: Q1 - How to find duplicates in a given table Q2 -> How to delete duplicates Q3 -> difference berween union ana union all Q4 -> diffrence between rank.row number and dense rank Q5 -> employees who are not present in deparament table Q6 > second highest salary in each dep Q7 -> find all transaction done by Shilpa Q8 -> self join, manager salary > emp salary Q9 -> Joins Left join/äner join Q10 -› update query to swap gender
Hey Ankit, I'm running below query for deleting duplicates -Q2 : WITH cte as (SELECT * , row_number() OVER( PARTITION BY emp_id ORDER BY emp_id ) AS rn FROM emp) DELETE FROM cte WHERE rn>1 but, I'm getting error : "Error Code: 1288. The target table cte of the DELETE is not updatable" . Kindly reply. TIA 😇
Ankit bhai if you have time can you solve some hacker rank test questions for better understanding for people who are learning sql newly..that would be helpfull
Thanks for this Video. I have one doubt, Question number 2. How to delete duplicates from a table? Shall we do it in mysql? I tried but I didn't get correct result.
I asked a question a employee enters and leaves office and he swipes in and swipes out in multiple instances, so I want to find the total office hours he spent in the office( he may come at 10 PM and take a break at 12:30 AM in that case it shows that he is spending 2 hours in that day but that's not true right), so now I have to calculate total time he spent employee id, Office_swipe_record given
Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table? @ankit
Hi Ankit, what if I have same rows with all same values in 2 rows, how can I do that.? As the code you explained is not working in MySQL workbench. Thanks!
1. We might need the primary key in the original table. Without that how can you tell database which record to delete, if you can anyhow tell database 😂 that delete the records which I mentioned it will be exactly same as duplicates so everything will be deleted. First u need to assign primary key. 2. MySQL 8+ versions doesn't support deleting data from cte( it's a temporary result set so logically it should not change the original table. Not sure about ssis implementation. ) so u have to reference the original table after selecting duplicate records u gotta tell database to delete those using a subquery. Do this: With cte as (Select * , row_number() over(partion by all the columns names) as rn From Ur_table_name) Delete from ur_table_name where id in (select * from cte where rn > 1)
Your approach to Delete duplicate valuse from the table is not working in my sql (gettting this pop up "The target table cte1 of the DELETE is not updatable")
Hi Ankit, the only doubt I have is while deleting duplicate records, we deleted from CTE and not actual employee table, how records were deleted from emp table?
Hi Aman , glad you asked this question. Since the CTE is referring to the table directly, it was possible. This might not be possible in all the databases but works in SQL server.
@@ankitbansal6 Right. I tried and even the below query is not working in MySQL. DELETE FROM (select *, row_number() over(partition by empno) as rn from emp) where rn>1;
@@ankitbansal6 Hi Ankit, I am also facing same issue. I assume CTE will delete from parent table. it fails with "The target table CTE of the DELETE is not updatable "
Hi Ankit , i start following your tutorial , it is really helpful . nut i am not able to connect SSMS at my local computer , can you please help me what should be server name . i am using SSMS 2019
Master the art of with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch.
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
Course contains 2 portfolio projects and a bonus session on Tableau.
100+ interview problems to crack any SQL INTERVIEW.
Is it in udemy ?
@@avinashmishra4995 nope
@@ankitbansal6 bro i really need your help.. is there any way i can reach out to you..
Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table?
Bro you have taught everything for free in your channel only, why would I will but the course.
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);
insert into emp
values
(1, 'Ankit', 100,10000, 4, 39;
insert into emp
values (2, 'Mohit', 100, 15000, 5, 48);
insert into emp
values (3, 'Vikas', 100, 10000,4,37);
insert into emp
values (4, 'Rohit', 100, 5000, 2, 16);
insert into emp
values (5, 'Mudit', 200, 12000, 6,55);
insert into emp
values (6, 'Agam', 200, 12000,2, 14);
insert into emp
values (7, 'Sanjay', 200, 9000, 2,13);
insert into emp
values (8, 'Ashish', 200,5000,2,12);
insert into emp
values (9, 'Mukesh',300,6000,6,51);
insert into emp
values (10, 'Rakesh',300,7000,6,50);
Thank you 😊
thanks
god bless you
create table orders(
customer_name char(10),
order_date date,
order_amount int,
customer_gender char(6)
);
insert into orders values('Shilpa','2020-01-01',10000,'Male');
insert into orders values('Rahul','2020-01-02',12000,'Female');
insert into orders values('Shilpa','2020-01-02',12000,'Male');
insert into orders values('Rohit','2020-01-03',15000,'Female');
insert into orders values('Shilpa','2020-01-03',14000,'Male');
create table department(
dept_id int,
dept_name varchar(10)
);
insert into department values(100,'Analytics');
insert into department values(300,'IT');
This is my one-stop video before every interview for the past years.
Brushes up my SQL skills like no other
For the First time in 10 years, I am feeling confident with using SQL, only after watching your series of videos. Earlier I used Python to do data preprocessing after doing Vanilla Select * from Table Statement, which was not a recommended approach for memory efficiency.
This is great. Best place to play with data is where it is residing 😊
Bang on ! Started product based companies interview prepration for Sr. DE role ( Google/Microsoft/Ubder/Amazon/Expedia / AirBnB) etc...and your content is crisp and clear. Thanks
🙏🙏
DDL and Insert values for emp table.
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);
insert into emp
values
(1, 'Ankit', 100,10000, 4, 39),
(2, 'Mohit', 100, 15000, 5, 48),
(3, 'Vikas', 100, 10000,4,37),
(4, 'Rohit', 100, 5000, 2, 16),
(5, 'Mudit', 200, 12000, 6,55),
(6, 'Agam', 200, 12000,2, 14),
(7, 'Sanjay', 200, 9000, 2,13),
(8, 'Ashish', 200,5000,2,12),
(9, 'Mukesh',300,6000,6,51),
(10, 'Rakesh',300,7000,6,50);
Thanks brother 😊
@@ankitbansal6 welcome ☺
Literally great.
In all my SQL interviews one or the other is asked.
Subscribed
Crisp & insightful
I am watching your one video each morning.
So one day I will work as data engineer in good product based company.
Thats a great way of preparing yourself 😊🙏
Wow it wsd asked today delete duplicates..thanks for the videos.it is really really helpful
Hi Ankit ji,
I seen some of the videos and those are very interesting, and useful.
I request you to create a video on SQL Index in details with examples.
1. Before creating index how the data loding in page level. And after creating index what kind of table ( page level) structure will be.
2. What will be structure while create only cluster and only noncluster and both.
Mean every one can understand beginners to exp students.
Waiting for your new video.
Very nice 👍❤
Thnks for the video. Very helpful 👍
I solved it using self join: as we are performing row level aggregation, I guess this is a good case for that:
SELECT DISTINCT(e1.emp_id),
e2.val as salary,
e3.val as bonus,
e4.val as hike_percent
FROM emp_compensation e1
JOIN emp_compensation e2
ON e1.emp_id = e2.emp_id AND e2.salary_component_type = 'salary'
JOIN emp_compensation e3
ON e1.emp_id = e3.emp_id AND e3.salary_component_type = 'bonus'
JOIN emp_compensation e4
ON e1.emp_id = e4.emp_id AND e4.salary_component_type = 'hike_percent';
Approach 2:
with stats as (
SELECT emp_id,
CASE WHEN salary_component_type = 'salary' THEN val END as salary,
CASE WHEN salary_component_type = 'bonus' THEN val END as bonus,
CASE WHEN salary_component_type = 'hike_percent' THEN val END as hike_percent
FROM emp_compensation)
SELECT emp_id,
SUM(salary) as salary,
SUM(bonus) as bonus,
SUM(hike_percent) as hike_percent
FROM stats
GROUP BY emp_id
Sorry I wanted to post in the section of this question: ua-cam.com/video/O6mDdUIvx9k/v-deo.html
@Md Jahidul Islam Razan - Approach 1 - 2.44 seconds vs Approach 2 - 1.31 seconds , i have tested in databricks community edition ,since underlying database is usually mysql, postgressql i think, CTE is more quicker to output both approaches are good !
Very Useful...I hope part2 will come soon
Yes it will
You always rock sir I love watching your video's and commenting too 💯
I was asked 3 questions out of 5 from your list. Thanks alot
I just discovered you, subscribed you and started watching your videos. I was asked a question in an interview which is similar to q 6 but a li'l twist in it.
Q - get 2nd highest salary from each dept and if a dept does not have a second salary then first salary should appear in result.
Hello - How to solve to bring the first highest record if second highest is not available. appreciate any suggestions.
@@rohitas7586
Query for getting 2nd highest if not present then 1st highest-
with cte as (
select * from (
select *, dense_rank() over(partition by department_id order by salary desc) as rnk
from emp
) a
)
select distinct emp.department_id,
coalesce(cte.emp_id, first.emp_id),
coalesce(cte.emp_name, first.emp_name),
coalesce(cte.manager_id, first.manager_id),
coalesce(cte.salary, first.salary) as salary
from emp left join cte on emp.department_id = cte.department_id and cte.rnk=2
left join cte as first on emp. department_id = first. department_id and first.rnk=1
order by emp.department_id
;
DDL for emp table and insert query-
create table emp(
emp_id int,
emp_name varchar(255),
department_id varchar(255),
salary int,
manager_id int);
insert into emp values(2,'Mohit', '100', 15000, 5),
(3, 'Vikas','100',10000,4),
(1,'Ankit','100',10000,4),
(4,'Rohit','100',5000,2),
(5,'Mudit','200',12000,6),
(6,'Agam','200',12000,2),
(7,'Sanjay','200',9000,2),
(8,'Ashish','200',5000,2),
(1,'Saurabh','900',12000,2);
Yes, you are absolutely right, Many questions are based on your teaching. Thanks again for this great help Ankit!!
Good to know 😊
very simple way to explain...Nice!
create table department(
dept_id int,
dept_name varchar(10)
);
insert into department values(100,'Analytics');
insert into department values(300,'IT');
create table orders(
customer_name char(10),
order_date date,
order_amount int,
customer_gender char(6)
);
insert into orders values('Shilpa','2020-01-01',10000,'Male');
insert into orders values('Rahul','2020-01-02',12000,'Female');
insert into orders values('Shilpa','2020-01-02',12000,'Male');
insert into orders values('Rohit','2020-01-03',15000,'Female');
insert into orders values('Shilpa','2020-01-03',14000,'Male');
Your videos are very good and helpful. Thank you for providing data with create and insert statements.
Great video! Loved that last question. Subbed and eager to practice more SQL with your other videos
🙏🙏
keep doing more videos like it. It really helped me in my job interview today. I am able to answer the question of how to remove duplicates from a table because of your videos.
Great job!
Amazing video, tomorrow I have an interview, I was clueless how to start and where to start, now I know what to do for tomorrow :))
Thanks for sharing :)
Best of luck!
Thank you sir :))
@@krishnabohidar7226 How did the interview go ?
I have subscribed and liked . you are doing wonderful job
Thank you so much 😀
Thanks for the Video
Hi Ankit, first of all this is really a great initiative that you are sharing all this knowledge and explaining the complex queries in a very easy way.
And because of this I am really glad to tell you that your channel has majorly helped me to crack one of the SQL interview. Thank you so much for such a great effort and we all wish to see some more complex queries and concepts in coming future, once again thank you so much. Wish you a great health!
Hey for which position you cleared the interview. And you cleared it as a fresher or experienced. Please reply.
Good explanation👏 Please do more intermediate and advanced level of concepts in SQL.
You just earned a subscribe from me bro. I haven't used SQL in years, but due to a career change I'm planning to have soon, I'll need to learn more about SQL in-depth alongside bettering my skillset in C++ programming.
Cool. Thank you 😊
Sir by looking at this video it seems SQL is very easy but actually it's not so easy sir, in interview same questions I faced nervous and end up giving wrong query.
Ankit sir added humour silently in this video with q10 😃
Thanks sir for this amazing video
Most welcome
Really Master class videos Ankit, Thanks.
but CTE doesn't support delete operations right...I tried it but got below error
"The target table cte of the DELETE is not updatable"
with cte as
(select *,
row_number() over (partition by md5(concat_ws(",",emp_id,emp_name,emp_age))) as dup_row_id
from employee)
delete from cte where dup_row_id>1;
It works only in SQL server
@@ankitbansal6 ok. thanks for replying 😊
@@ankitbansal6 What should be the approach for MYSQL?. Thanks in advance
I guess we can use subquery table as a normal table to display information using alias and where clause , but when it comes to comparison it needs to return only 1 value :) !!
revised today for deloiite interview and suscribed too
Cool . all the best.
Hi Sir,
Big thank to you i learn lot of things in sql after watching your video .
I want to add to solution from my side please let me know this will fine or not?
1) with cte as (
select *,row_number() over(partition by emp_id order by time desc) as rnk from hospital)
select count(*) from cte where rnk =1 and action ="in";
2) select count(*) as total_person from hospital h where time =(Select max(time) from hospital h1 where h1.emp_id = h.emp_id) and action ="in";
Waiting for your response.
Again big thank to you.
Superb video..liked every second of the video 💖
Thank you! Cheers!
Thanks Ankit its very helpful in interivews wish i had come across your videos early.. thnks much
Thank you 😊
Wow such a nice content. Please zoom a bit while making videos. Because most audience watch it over phone it strain the eyes
Sure
simple and clear explanation!!
Q7. MSSQL does not depend on case-sensitive but in the Oracle is depend on the case-sensitive
Thanks for this video.
Glad you liked it 😊
yeah it was really important vid as see for the interveiw sceneriao
Just landed here bro you are doing amazing job
In the first question where we wanna find out the duplicates, we knew that the duplicates would be as per emp_id. How about if we wanna check if the entire row is unique or not rather than checking any particular column?
Awesome
you don't need the motivation from others because, you yourself is a motivation 🤗
🙏
Very Good explanation!
Glad it was helpful!
Thanks a lot, Learned lots of new concepts.
Glad to hear that!
Thanks ankit, ur videos always encourage and boost confidence to prac more n go beyond our limit. Thanks Man
Keep it up
for better reach, keep creating content
sure.
Hi Ankit, loved your all videos. Looking forward to see a video on subqueries,
Sure 🙂
5/123
Stuck with 2 ques , coz i'm using mysql
thanks, it actually let me through so i could download it.
You are doing really awesome. Please keep doing this.
One suggestion: Please don't forget to add create table script.
Thank you. Scripts are there in most of the video description box. Might have missed few in my initial videos. Will put them soon.
@@ankitbansal6 Cool
Great content. Thank you Ankit
Thanks a ton
@Ankit Bansal bhai if we have left whats the use of right join ?
Not required 😁
Thank for the video 🙏
Just loving your content.. Very well explained.
Happy to hear that!
Hey Ankit! I was thinking about that delete duplicate question. U r deleting the duplicate data from cte which is a temporary result set , right? It is working fine but how it's deleting from original table?
MySQL doesn't support this from 8+ versions I guess..we have to use that cte query like a subquery in mysql.
But in ssis it is working. Even in leetcode !
But I think it is always better to reference the original table rather than querying from cte i.e a temporary result set. Selection is fine with cte but dml operation!
Let me know about your thoughts, I would love to hear!
very helpful ..thanks
Glad it was helpful!
thanks Ankit..good tutorial..
Thank you 😊
In 2nd Question of removing duplicates: We are deleting values from the CTE table which is a TEMP table. Then how it is deleting values from the MAIN table?
Good one .. !!
Glad you liked it
with CTE as
(
select * ,row_number() over(partition by emp_id order by emp_id) as rnk from emp order by emp_id asc
)
delete from CTE where rnk > 1
From CTE we can't delete records?
Thank you so much.
Make vidoes on JOINS.
Sure
You just earned a subscriber really great content🥺
It will be helpful if you can create videos on views and stored procedures
Tqsm for awesome explanation 🔥
with dup as (
select
ROW_NUMBER() over(partition by emp_id order by emp_id desc) as Find_duplicate
from emp)
delete from emp
where emp_id in (
select *
from dup
where Find_duplicate > 1);
Very helpful sir, thank you!
You are welcome!
Very well explained... 👌
Great video, I took down some notes!
Questions:
Q1 - How to find duplicates in a given table
Q2 -> How to delete duplicates
Q3 -> difference berween union ana union all
Q4 -> diffrence between rank.row number and dense rank
Q5 -> employees who are not present in deparament table
Q6 > second highest salary in each dep
Q7 -> find all transaction done by Shilpa
Q8 -> self join, manager salary > emp salary
Q9 -> Joins Left join/äner join
Q10 -› update query to swap gender
5th question can we also done used left anti right?
like where dep.deptid is null?
Hey Ankit,
I'm running below query for deleting duplicates -Q2 :
WITH cte as (SELECT * , row_number() OVER( PARTITION BY emp_id ORDER BY emp_id ) AS rn FROM emp)
DELETE FROM cte WHERE rn>1
but, I'm getting error : "Error Code: 1288. The target table cte of the DELETE is not updatable" .
Kindly reply. TIA 😇
Try delete * . Which rdbms are you using?
hi , did you got the solution for the above query
coz i am facing the same issue
@@yatinshekhar787 no I couldn't
before delete alias a will be give
with cte as ( select * ,row_number() over(partition by emp_id order by emp_id) as RN from emp)
delete from cte where RN > 1
Bro good vide class Bren you have to increase your sound
Ankit bhai if you have time can you solve some hacker rank test questions for better understanding for people who are learning sql newly..that would be helpfull
Do you have some links to the good problems?
hi Ankit , can you please make some videos for product based companies. Thank yoou for your effort. Appriciatable
I have a lot of them. Check out complex SQL playlist
please make a part 2 of this video Ankit.
thank you for your efforts
Sure I will
Thanks for this Video. I have one doubt, Question number 2. How to delete duplicates from a table? Shall we do it in mysql? I tried but I didn't get correct result.
+1
I asked a question a employee enters and leaves office and he swipes in and swipes out in multiple instances, so I want to find the total office hours he spent in the office( he may come at 10 PM and take a break at 12:30 AM in that case it shows that he is spending 2 hours in that day but that's not true right), so now I have to calculate total time he spent
employee id, Office_swipe_record given
Delete duplicate records query in not working in databricks notebook for deleting duplicate records from a table.. is there any way to do this in databricks notebook table?
@ankit
Thanks for this!
Thanks brother, nice explanation
Thank you
Beautiful presentation
Thank you! Cheers!
Hi Ankit, what if I have same rows with all same values in 2 rows, how can I do that.? As the code you explained is not working in MySQL workbench. Thanks!
1. We might need the primary key in the original table. Without that how can you tell database which record to delete, if you can anyhow tell database 😂 that delete the records which I mentioned it will be exactly same as duplicates so everything will be deleted.
First u need to assign primary key.
2. MySQL 8+ versions doesn't support deleting data from cte( it's a temporary result set so logically it should not change the original table. Not sure about ssis implementation. ) so u have to reference the original table after selecting duplicate records u gotta tell database to delete those using a subquery.
Do this:
With cte as
(Select * , row_number() over(partion by all the columns names) as rn
From Ur_table_name)
Delete from ur_table_name where id in (select * from cte where rn > 1)
Good content, i have a question why table name used as emp1, it should be emp right?
Simply
Your approach to Delete duplicate valuse from the table is not working in my sql (gettting this pop up "The target table cte1 of the DELETE is not updatable")
#For swap gender :
update orders
set customer_gender=IF(customer_gender='Male','Female','Male');
Hi Ankit, the only doubt I have is while deleting duplicate records, we deleted from CTE and not actual employee table, how records were deleted from emp table?
Hi Aman , glad you asked this question. Since the CTE is referring to the table directly, it was possible. This might not be possible in all the databases but works in SQL server.
@@ankitbansal6 Right. I tried and even the below query is not working in MySQL.
DELETE FROM (select *, row_number() over(partition by empno) as rn from emp)
where rn>1;
@@ankitbansal6 Hi Ankit, I am also facing same issue. I assume CTE will delete from parent table. it fails with "The target table CTE of the DELETE is not updatable
"
@@DataRevolution10 it works only in SQL server
+1
Ankit, I love your educational content. Is it possible for you to record the whole PL/SQL course for beginner/Intermediates?
Thank you. I will plan in future 🙂
In the 2 question, delete from cte is written so how the records got deleted from original table
Thanks
Just wanted to know can we use left anti join for the 5th question?
Diff between count* and count1
Thanks for adding 😊
Hi Ankit , i start following your tutorial , it is really helpful . nut i am not able to connect SSMS at my local computer , can you please help me what should be server name . i am using SSMS 2019
Localhost
Make sure you have installed SQL server as well. ssms is just a client to access data base
finished watching