Steps for Select Statement Process in SGA : >SELECT * FROM EMP; step 1 when user fires update statement first it will start user processes step 2 user processes to server processes with the help of user processe interface step 3 server processes to… Read More
Delete duplicate records from table Dear Readers, In this article we will see ”Delete duplicate records from table” Script if one column having duplicate records:
|
1 2 3 4 5 6 7 8 9 |
DELETE from scott.test1 a0 WHERE a0.rowid NOT IN ( SELECT max(a1.rowid) FROM scott.test1 a1 GROUP BY a1.a HAVING count(*) > 1) AND (a0.a) IN (SELECT a2.a FROM scott.test1 a2 GROUP BY a2.a HAVING count(*) > 1); |
Script if two columns having duplicate records:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DELETE from scott.test2 a0 WHERE a0.rowid NOT IN ( SELECT max(a1.rowid) FROM scott.test2 a1 GROUP BY a1.NUM, a1.NAME HAVING count(*) > 1) AND (a0.NUM, a0.name) IN (SELECT a2.NUM, a2.name FROM scott.test2 a2 GROUP BY a2.NUM, a2.NAME HAVING count(*) > 1); |
Deleting duplicate records involving multiple columns :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DELETE from EMP a0 WHERE a0.rowid NOT IN ( SELECT max(a1.rowid) FROM EMP a1 GROUP BY a1.COL1, a1.COL2, a1.COL3, a1.COL4 HAVING count(*) > 1) AND (a0.COL1, a0.COL2, a0.COL3, a0.COL4) IN (SELECT a2.COL1, a2.COL2, a2.COL3, a2.COL4 FROM EMP a2 GROUP BY a2.COL1, a2.COL2, a2.COL3, a2.COL4 HAVING count(*) > 1); |
Thank you for… Read More
USER MANAGEMENT How to create user? Syntax sys>> create user <username> identified by <password>; Create user u1 identified by u1; How to assign the privileges to user? Grant connect,resource to u1; How to assign the tablespace to user? Alter user u1 default tablespace userdata; How… Read More
