CODEWITHSUNDEEP
liquibase
user558213
user558213

Reputation: 69

Insert into table if entry does not exist

HI all I want to create a changeset which inserts which iterates over all entries of a table an looks into another table by the primaryKey. If in this table there is no entry for that primaryKey, 2 inserts in 2 different tables should be performed. Does anyone know how i can solve this?

Greets and thanks for your help

Upvotes: 1

Views: 5292

Answers (1)

Diver
Diver

Reputation: 1608

table1 (data to use for check)  I'm assuming you want to pull some data from here, if not replace table1.col3 below with the data you want.
table2 (data to check against)  Assumes your FK column is table1_id
table3 (table to insert to)
table4 (table to insert to)

I would use 2 change sets (assuming the data in tables 1 and 2 will not change while running them)

<changeSet>
  <sql>insert into table3 (col1, col2) (select table1.col3, 'val1' from table1 where table1.id not in(select table2.table1_id from table2))</sql>
</changeSet>
<changeSet>
  <sql>insert into table4 (col1, col2) (select table1.col3, 'val1' from table1 where table1.id not in(select table2.table1_id from table2))</sql>
</changeSet>

Upvotes: 2

Related Questions