HomeНаука и техникаRelated VideosMore From: Kishan Mashru

Merge Statement in Oracle SQL with Example (UPDATE/DELETE or INSERT)

42 ratings | 10407 views
This video demonstrates how to write a Merge Statement in oracle sql, how it is useful in doing a conditional update or delete or both of them when the condition between the target table and source table is evaluated to true, and how we can insert the record into the target table when condition is evaluated to false. The video also shows the use of UPDATE, DELETE and INSERT with Merge, and also the various possible ways to use a Merge statement!!!
Html code for embedding videos on your blog
Text Comments (11)
Aniket Patil (11 months ago)
sir before uploading any video first check at your side and then publicized it. voice is not clear . video presentation and voice are not in sync..
Axwack (1 year ago)
Work on your presentation it’s not dry coherent or audible
Smruti Ranjan (1 year ago)
Teaching style is superb carry on.thank u
Kishan Mashru (1 year ago)
Thanks, am gald you enjoyed watching it.
Smruti Ranjan (1 year ago)
Your voice is not audible
Smruti Ranjan (1 year ago)
Kishan Mashru . now it's perfect
Kishan Mashru (1 year ago)
Ya, a sorry... have taken care of it in my recent videos but can't find a way to increase the volume of this upload, try using headphones, that will help!!!
Nagaraju Kommiri (1 year ago)
Hi Kishan, a)can you please help me i am not able to delete alone where in matched clause i am not able to delete in this case is there any wrong in my practice begin INSERT INTO EMP_DUMMY1 select empno,ename,job,mgr,hiredate,sal,comm,deptno from (select e.*, row_number() over(order by ROWNUM) rn from emp e) where rn between 1 and 10; INSERT INTO EMP_DUMMY2 select empno,ename,job,mgr,hiredate,sal,comm,deptno from (select e.*, row_number() over(order by ROWNUM) rn from emp e) where rn between 11 and 14; end; merge into EMP_DUMMY1 e1 using emp e2 on (e2.empno=e1.empno) when not matched then --update set comm = sal/10 DELETE where e2.comm is null when not matched then insert values(e2.empno,e2.ename,e2.job,e2.mgr,e2.hiredate,e2.sal,e2.comm,e2.deptno); b) here i have one more interview question actually when i need to insert two target table at a time like you know lets say target table having 100 records 80 records i need to insert into target1 and remaining are in target2
Kishan Mashru (1 year ago)
Welcome :)
Nagaraju Kommiri (1 year ago)
Thanks Kishan, i like your explanation of interview questions
Kishan Mashru (1 year ago)
Hello, a) An optional DELETE WHERE clause can be added to the MATCHED clause to clean up after a merge operation. Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. Depending on which table the DELETE WHERE references, it can target the rows prior or post UPDATE. So you cannot have a DELETE without an UPDATE b) you can use PIVOT INSERT to perform such task.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.