- Insert into a table only if row does not exists
Note, there may be race conditions with this approach. In our case we were running it in liquibase scripts, and we didn't have multiple servers running in parallel so this wasn't an issue
- Delete duplicates
After all my inserts, I ended up with some unexpected duplicates. so I had to delete them.
I was able to find te duplicate rows easily enough. I searched for all rows with a the same name having a count >1 to find duplicates.
However to delete them we had foreign key relationships, that meant that we could only delte the newly created rows. But these weren't easily identifiable. We decided on the following approach.
We ran the following expression twice. Once with max(rowid) and once with min(rowid), since the table had foreign key dependencies it couldn't be reliably be delted, so this way I managed to get all the duplicates. note if you have many duplicates this may prove more problematic.