LINUX-Quick-Start

http://linux-cottage.blogspot.in/

 Delete Duplicate Records from Table.

CREATE TABLE DBACottage_EMP
( empno number
, ename varchar2(10)
);


It holds the following data:


INSERT INTO DBACottage_EMP (empno, ename) VALUES (1, 'PRASHANSH');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (10, 'SUVARNA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (20, 'SHIKHA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (30, 'PANKAJ');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (40, 'VIVEK');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (50, 'GAURAV');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (60, 'GOSHO');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (70, 'AMRITESH');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (80, 'SHIKHA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (90, 'SHIKHA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (100, 'PRASHANT');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (101, 'ALKESH');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (102, 'SHIKHA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (103, 'SHIKHA');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (104, 'PUSHPESH');
INSERT INTO DBACottage_EMP (empno, ename) VALUES (105, 'LOKESH');
COMMIT;

Answer:=>

DELETE FROM DBACottage_emp
 WHERE empno NOT IN (SELECT MIN(empno)
                       FROM DBACottage_emp
                      GROUP BY ename);


Ans:=>
DELETE FROM DBACottage_emp
 WHERE empno NOT IN (SELECT MIN(empno)
                       FROM DBACottage_emp
                      GROUP BY ename);

Implementation :=>

SYS@axis AS SYSDBA> create user prashansh
  2  identified by dbacottage
  3  default tablespace users
  4  quota 10M on users
  5  account unlock;

User created.

SYS@axis AS SYSDBA> grant create table ,create any table,create session to prash
ansh;

Grant succeeded.

SYS@axis AS SYSDBA> connect prashansh
Enter password:
Connected.
PRASHANSH@axis > CREATE TABLE DBACottage_EMP
  2  ( empno number
  3  , ename varchar2(10)
  4  );

Table created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (1, 'PRASHANSH
');

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (10, 'SUVARNA'
);

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (20, 'SHIKHA')
;

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (30, 'PANKAJ')
;

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (40, 'VIVEK');


1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (50, 'GAURAV')
;

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (60, 'GOSHO');


1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (70, 'AMRITESH
');

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (80, 'SHIKHA')
;

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (90, 'SHIKHA')
;

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (100, 'PRASHAN
T');

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (101, 'ALKESH'
);

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (102, 'SHIKHA'
);

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (103, 'SHIKHA'
);

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (104, 'PUSHPES
H');

1 row created.

PRASHANSH@axis > INSERT INTO DBACottage_EMP (empno, ename) VALUES (105, 'LOKESH'
);

1 row created.

PRASHANSH@axis > COMMIT;

Commit complete.

PRASHANSH@axis > select * from dbacottage_emp;

     EMPNO ENAME
---------- ----------
         1 PRASHANSH
        10 SUVARNA
        20 SHIKHA
        30 PANKAJ
        40 VIVEK
        50 GAURAV
        60 GOSHO
        70 AMRITESH
        80 SHIKHA
        90 SHIKHA
       100 PRASHANT

     EMPNO ENAME
---------- ----------
       101 ALKESH
       102 SHIKHA
       103 SHIKHA
       104 PUSHPESH
       105 LOKESH

16 rows selected.

PRASHANSH@axis > DELETE FROM DBACottage_emp
  2   WHERE empno NOT IN (SELECT MIN(empno)
  3                         FROM DBACottage_emp
  4                        GROUP BY ename);

4 rows deleted.

PRASHANSH@axis > commit;

Commit complete.

PRASHANSH@axis > select * from dbacottage_emp;

     EMPNO ENAME
---------- ----------
         1 PRASHANSH
        10 SUVARNA
        20 SHIKHA
        30 PANKAJ
        40 VIVEK
        50 GAURAV
        60 GOSHO
        70 AMRITESH
       100 PRASHANT
       101 ALKESH
       104 PUSHPESH

     EMPNO ENAME
---------- ----------
       105 LOKESH

12 rows selected.

Inside the Oracle Database Cloud Service