Merge - Oracle and populate in two different tables based on count condition-CodePudding

I have a table name "TABLE1" with 4 columns COLUMN1, COLUMN2, FREQ, CNT I have a result table name "RESULT1" with 4 columns COLUMN1, COLUMN2, FREQ I have a result table name "RESULT2" with 4 columns COLUMN1, COLUMN2, FREQ

CREATE TABLE TABLE1 ( COLUMN1 VARCHAR2(4), COLUMN2 VARCHAR2(4), FREQ NUMBER, CNT NUMBER);

INSERT INTO TABLE1(COLUMN1, COLUMN2, FREQ) VALUES ('1234', 'ABCD', 1);
INSERT INTO TABLE1(COLUMN1, COLUMN2, FREQ) VALUES ('1234', 'ACBD', 1);
INSERT INTO TABLE1(COLUMN1, COLUMN2, FREQ) VALUES ('1234', 'ABDC', 1);
INSERT INTO TABLE1(COLUMN1, COLUMN2, FREQ) VALUES ('1342', 'DAFY', 1);
INSERT INTO TABLE1(COLUMN1, COLUMN2, FREQ) VALUES ('1423', 'CBAD', 1);

I want to update the CNT column in TABLE1 based on number of times COLUMN1 is repeated.

The result in TABLE1:

  COLUMN1     COLUMN2   FREQ  CNT
 -----------------------------------
   1234         ABCD     1     3
   1234         ACBD     1     3
   1234         ABDC     1     3
   1342         DAFY     1     1
   1423         CBAD     1     1

Once we get above result,

based on CNT value, if CNT =1 insert in RESULT1 table else CNT > 1 insert those records in RESULT2 table like

RESULT2 table

     COLUMN1     COLUMN2   FREQ 
 -----------------------------------
   1234         ABCD       1     
   1234         ACBD       1     
   1234         ABDC       1

RESULT1 table

     COLUMN1     COLUMN2   FREQ 
 -----------------------------------
    1342         DAFY      1     
    1423         CBAD      1

I tried using MERGE statement to populate but not able to get answer showing some syntax error.

CodePudding user response：

how about the oracle insert all. lets you conditionally insert into multiple tables based on a condition.

insert All
When cnt > 1 THEN
into result1 
else
into result2 
select column1, column2, freq, count(*) over (partition by column1) as cnt from table1;

here is the sql fiddle. http://sqlfiddle.com/#!4/d6f20/5

CodePudding user response：

From my point of view, a simple way is the best way.

This is the starting point:

SQL> select * from table1;

COLU COLU       FREQ        CNT
---- ---- ---------- ----------
1234 ABCD          1
1234 ACBD          1
1234 ABDC          1
1342 DAFY          1
1423 CBAD          1

Update TABLE1.CNT:

SQL> update table1 a set
  2    a.cnt = (select count(*)
  3             from table1 b
  4             where b.column1 = a.column1
  5            );

5 rows updated.

SQL> select * from table1;

COLU COLU       FREQ        CNT
---- ---- ---------- ----------
1234 ABCD          1          3
1234 ACBD          1          3
1234 ABDC          1          3
1342 DAFY          1          1
1423 CBAD          1          1

Rows whose cnt = 1 go into result1:

SQL> insert into result1 (column1, column2, freq)
  2    select column1, column2, freq
  3    from table1
  4    where cnt = 1;

2 rows created.

SQL> select * from result1;

COLU COLU       FREQ
---- ---- ----------
1342 DAFY          1
1423 CBAD          1

Rows whose cnt > 1 go into result2:

SQL> insert into result2 (column1, column2, freq)
  2    select column1, column2, freq
  3    from table1
  4    where cnt > 1;

3 rows created.

SQL> select * from result2;

COLU COLU       FREQ
---- ---- ----------
1234 ABCD          1
1234 ACBD          1
1234 ABDC          1

SQL>

The end.