(One table) insert rows

Question

This question is the continuation of this one.

I have the following table egr:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
+---------+------------+

I would like to insert missing groupids that the offid 2 does not have (compared to offid 1). Result would be:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
|       2 | 101        |   --> new row to insert
+---------+------------+

My try, based on the answer of my other question (not working):

INSERT INTO egr (offid, groupid)
  SELECT 2, egr1.groupid
  FROM egr AS egr1 
  WHERE egr1.offid = 1
  AND NOT EXISTS
    (select 1
                  from egr e2
                  where e2.groupid = egr1.groupid and 
                        e2.offid in (1, 2) and
                        e2.offid <> egr1.offid 
                 );

score 2 · Accepted Answer · answered May 22 '19 at 05:47

2

This may be easier to achieve using the except operator:

INSERT INTO egr (offid, groupid)
SELECT 2, groupid
FROM   egr
WHERE  offid = 1
EXCEPT
SELECT 2, groupid
FROM   egr
WHERE  offid = 2

answered May 22 '19 at 05:47

Mureinik

297,002
52
306
350

Yes this works: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=7acea85a9ab1fecdf401f7d6f7eb89a8 (in your SQL fiddle as well: http://sqlfiddle.com/#!15/ef3f0/3) Although I would leave out the `WHERE offid = 1`part because you might have more than 2 values in reality. – S-Man May 22 '19 at 06:32
@S-Man the OP's requirement was to insert to `offid=2` the values that `offid=1` has and it hasn't. If you omit that from the where clause, you may insert values from `offid='something else'` – Mureinik May 22 '19 at 09:18

(One table) insert rows

1 Answers1

Linked