Insert new row of data in SQL table if the 2 column values do not exist

Question

I have a PostgreSQL table interactions with columns

AAId, IDId, S, BasicInfo, DetailedInfo, BN

AAID and IDId are FK to values referencing other tables.

There are around 1540 rows in the ID table and around 12 in the AA table.

Currently in the interactions table there are only around 40 rows for the AAId value = 12 I want to insert a row for all the missing IDId values.

I have searched, but cant find an answer to inserting rows like this. I am not overly confident with SQL, I can do basics but this is a little beyond me.

To clarify, I want to perform a kind of loop where,

for each IDId from 1-1540,
   if (the row with AAId = 12 and IDId(current IDId in the loop does not exist)
       insert a new row with,
           AAId = 12,
           IDId = current IDId in the loop,
           S = 1,
           BasicInfo = Unlikely
           DetailedInfo = Unlikely

Is there a way to do this in SQL?

user3738870 · Accepted Answer · 2022-12-02 08:55:17Z

1

Yes, this is possible. You can use data from different tables when inserting data to a table in Postgres. In your particular example, the following insert should work, as long as you have the correct primary/unique key in interactions, which is a combination of AAId and IDId:

INSERT INTO interactions (AAId, IDId, S, BasicInfo, DetailedInfo, BN)
SELECT 12, ID.ID, 1, 'Unlikely', 'Unlikely'
FROM ID
ON CONFLICT DO NOTHING;

ON CONFLICT DO NOTHING guarantees that the query will not fail when it tries to insert rows that already exist, based on the combination of AAId and IDId.

If you don't have the correct primary/unique key in interactions, you have to filter what IDs to insert manually:

INSERT INTO interactions (AAId, IDId, S, BasicInfo, DetailedInfo, BN)
SELECT 12, ID.ID, 1, 'Unlikely', 'Unlikely'
FROM ID
WHERE NOT EXISTS (
    SELECT * FROM interactions AS i
    WHERE i.AAId = 12 AND i.IDId = ID.ID
);

edited Dec 2, 2022 at 8:55

answered Dec 1, 2022 at 12:57

user3738870

1,6502 gold badges16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danny Jebb Over a year ago

Thanks for this. I am helping a friend out with a project where the developer went AWOL! It looks like they have only set up the interactions table with the ID as the PK and it is linked to lots of other data, so I cant amend the PK to use the AAId and IDId column!

user3738870 Over a year ago

It's also possible to do it if you don't have the correct key, I've extended my answer with that.

Collectives™ on Stack Overflow

Insert new row of data in SQL table if the 2 column values do not exist

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related