sql - compare rows between two ids with in the same table and insert the missing row for that id

Question

i have a table which has structure mentioned below let's say table name -> tab1

id   c1     c2    c3         c4
1    a      b   01-02-18     c          row1
1    o      b   01-02-18     c          row2
1    a      b   04-05-16     c          row3
1    n      g   01-02-18     d          row4
2    a      b   01-02-18     c          row5

So i want to insert id 1 rows to id 2. As data for row1 and row5 is same for column c1,c2,c3,c4 so i want to skip row1 to be inserted for id 2 .

Table should look like this

id   c1     c2    c3         c4
1    a      b   01-02-18     c          row1
1    o      b   01-02-18     c          row2
1    a      b   04-05-16     c          row3
1    n      g   01-02-18     d          row4
2    a      b   01-02-18     c          row5
2    o      b   01-02-18     c          row6
2    a      b   04-05-16     c          row7
2    n      g   01-02-18     d          row8

i have written this query but doesn't give me the expected result

for selecting the unique record based on column :

select Count(*) FROM tab1 A
WHERE   Not EXISTS 
(select * from  tab1  B where  A.c1 = B.c1 AND A.c2 = B.c2 AND A.c3= B.c3 
AND A.c4 = B.c4 
and B.id=2 )and A.id = 1;

for inserting the records

insert into rsk_mdl_sec_map_ts
select '2', c1, c2, c3, c4
FROM tab1 A
WHERE   Not EXISTS 
(select * from  tab1  B where  A.c1 = B.c1 AND A.c2 = B.c2 AND A.c3= B.c3 
AND A.c4 = B.c4 
and B.id=2 )and A.id = 1;

can anyone help what is wrong in this or suggest me some other approach to achieve the same . Thanks

标签： sqloracleoracle11g

Answer 1

首先构建一个查询，选择应该插入的行并忽略那些已经存在的行id=2：

SELECT * 
FROM tab1 t1
WHERE id = 1
  AND NOT EXISTS (
    SELECT 'anything' FROM tab1 t2
    WHERE t1.c1=t2.c1
      AND t1.c2=t2.c2
      AND t1.c3=t2.c3
      AND t1.c4=t2.c4
      AND id = 2
)

| ID | C1 | C2 |                    C3 | C4 |
|----|----|----|-----------------------|----|
|  1 |  o |  b | 2018-01-02 00:00:00.0 |  c |
|  1 |  a |  b | 2016-04-05 00:00:00.0 |  c |
|  1 |  n |  g | 2018-01-02 00:00:00.0 |  d |

演示：http ://sqlfiddle.com/#!4/d66fc/4

---

接下来，使用 INSERT ... SELECT .... 命令，只需将 INSERT 放在 SELECT 命令上方，并使用2常量作为 ID，并使用 DISTINCT 子句删除可能的重复项：

INSERT into tab1( id, c1, c2, c3, c4 )

SELECT DISTINCT 2, c1, c2, c3, c4
FROM tab1 t1
WHERE id = 1
  AND NOT EXISTS (
    SELECT 'anything' FROM tab1 t2
    WHERE t1.c1=t2.c1
      AND t1.c2=t2.c2
      AND t1.c3=t2.c3
      AND t1.c4=t2.c4
      AND id = 2
)