Insert into TABLE2 has count(CLASS) = 1-CodePudding

I have 2 tables(TABLE1 and TABLE2)

We fetch the data from TABLE1 and insert into TABLE2 having specific column count = 1

TABLE1 and TABLE2 has same columns (ROLLNO, NAME, CLASS)

CREATE TABLE TABLE1(ROLLNO NUMBER(3), NAME VARCHAR2(4), CLASS VARCHAR2(2));
CREATE INDEX IDX1 ON TABLE1(CLASS);

CREATE TABLE TABLE2(ROLLNO NUMBER(3), NAME VARCHAR2(4), CLASS VARCHAR2(2));
CREATE INDEX IDX2 ON TABLE2(CLASS);

INSERT INTO TABLE1 VALUES(100, 'JOHN', '01');
INSERT INTO TABLE1 VALUES(101, 'ROHN', '01');
INSERT INTO TABLE1 VALUES(102, 'MAYA', '02');
INSERT INTO TABLE1 VALUES(103, 'NAYA', '03');
INSERT INTO TABLE1 VALUES(104, 'SANA', '04');

We need to insert the records into TABLE2 from TABLE1 having records based on count of CLASS column value = 1 only.

TABLE2 has following data after fetching data from TABLE1 based on above condition

ROLLNO    NAME     CLASS
-------------------------
  102     MAYA       02
  103     NAYA       03
  104     SANA       04

CodePudding user response：

You can use this query:

SELECT CLASS FROM TABLE1 GROUP BY CLASS HAVING COUNT(*) = 1

in the INSERT statement to get only the CLASSes which occur only once in TABLE1:

INSERT INTO TABLE2
WITH cte AS (SELECT CLASS FROM TABLE1 GROUP BY CLASS HAVING COUNT(*) = 1)
SELECT * 
FROM TABLE1
WHERE CLASS IN (SELECT CLASS FROM cte);

See the demo.

CodePudding user response：

Aggregate table1 by class; since you only need to insert for groups with a row count of 1, you can select max(rollno) and max(name) (or min, it doesn't matter) for each group and use that for insertion. In those cases (when the row count is 1), the max or min is the same as the one value (in the one row) in the group.

This way you only need to scan table1 once, rather than twice.

insert into table2 (rollno, name, class)
    select max(rollno), max(name), class
    from   table1
    group  by class
    having count(*) = 1
;