"INSERT...ON DUPLICATE KEY UPDATE" in Django 4.1
I upgraded Screener to Django 4.1 last week. I was surprised to see the support for conflicting updates in bulk_create method. This uses "insert...on duplicate key update" under the hood. [Django Docs].
INSERT...DUPLICATE UPDATE (MySQL docs) is one of my favourite SQL hacks. It allows us to create and update rows in bulk.
We pass the set of rows. The already existing rows are updated while the new ones are created. The "already existing" rows are detected on unique constraints. The good thing is that it is all done at SQL level and is very fast. It is 1 query to rule them all.
We have been using a custom function of INSERT...DUPLICATE KEY UPDATE query from day 1. We use it while pulling updates from our data providers.
We pull the updates, read the CSVs and pass all the rows in this query. We don't need to worry or check whether there are conflicting rows. The query handles all of that.