CODEWITHSUNDEEP
sqlmariadb
Jim Panse
Jim Panse

Reputation: 2270

Insert only if row with non uniqe foreign key doesn't already exist in MariaDB

I have a table A

id field
1 valueA
2 valueB
... ...

and a table B with a reference to table A

id fk_table_a field_a field_b ...
1 1 ... ... ...
2 2 ... ... ...
... ... ... ... ...

I wanna insert something on table B only if it doesn't have already any dataset that has a reference to A (via fk_table_a)

Unfortunately the foreign fk_table_a key isn't unique and i can't change this fact, so it is potential possible to insert another entry with same foreign key. But i don't want this in my case.

I use INSERT INTO ... SELECT statement to insert a whole bunch of rows into the table B ... the select for this is a bit more extensive but it looks something like this

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
   SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
   FROM other_table_1 ot1, 
   JOIN other_table_2 ot2 ...
   JOIN other_table_3 ot3 ...
   ....
#only insert the current dataset if it ot1.id_table_a doesnt exist already in table_b!

So for any dataset from the select i don't wanna have an entry in table_b, if there is already a dataset with the same fk_table_a ...

Any ideas?

Upvotes: 0

Views: 46

Answers (1)

Ananth MK
Ananth MK

Reputation: 392

Adding a WHERE clause to check non-existance of the id in other_table_1 may fix your problem. Try this,

INSERT INTO table_b (fk_table_a, field_a, field_b, ...)
  SELECT ot1.id_table_a, ot2.field_a, ot3.field_b
  FROM other_table_1 ot1, 
     JOIN other_table_2 ot2 ...
     JOIN other_table_3 ot3 ...
  WHERE ot1.id_table_a NOT IN (SELECT fk_table_a FROM table_b)

Upvotes: 1

Related Questions