Our php codes base has 6 different ways to do INSERT … ON DUPLICATE KEY UPDATE. It happened over years because the php framework is evolving and my team members come and go, although I won’t say we have a high turnover rate.

First, there are updateOrCreate and updateOrInsert in laravel. Their difference is already confusing. Check this article for some explanation, The Differences Between UpdateOrCreate And UpdateOrInsert in Laravel. They both generate 2 SQL clauses – first is SELECT, and second is UPDATE or INSERT depends on the result of first SELECT.

Second, because we use mysql, one senior guy introduced an open source project https://github.com/yadakhov/insert-on-duplicate-key to utilize INSERT … ON DUPLICATE KEY UPDATE on mysql.

Third, after we upgraded to laravel 9, we found laravel has upsert, introduced by https://github.com/laravel/framework/pull/34698. upsert will do INSERT … ON DUPLICATE KEY UPDATE on mysql too.

Fourth, a new guy joined the team and always use replace without knowing the exact difference between replace and insert on duplicate update (refer to What are practical differences between REPLACE and INSERT ... ON DUPLICATE KEY UPDATE in MySQL?)

Fifth, some member still writes his own 2 SQL clauses, select first then check the result to see if insert or update next. Maybe he is not familiar with INSERT … ON DUPLICATE KEY UPDATE or upsert， maybe he is just sloppy and our code review didn’t catch that too.

So now in our code bases we have six different ways to deal with insert or update. I am not sure often this happens for a code base existing for more than 5 years (this particular case maybe be extreme, but we do have 2 or 3 different ways to do basically the same thing in other case). But do I fix this ? Code review and refactor are always easier said than done (The fact that we are having this problem now is a clear sign we didn’t code review enough).

BTW, regarding the programming issue caused by 2 SQL clauses I asked a question on stackoverlfow. I am asking the question here from engineering point of view.