UPDATE if exists, INSERT if not in MySQL (AKA the UPSERT)

It's a bit of a long running joke that I'm not really a database guy, but let's just say that lately I've been exposed to a little more DB than usual and it's been quite a steep learning curve.

Anyway, this little feature in MySQL has proved pretty useful to me so I thought I'd make a note of it on my in-lieu-of-a-functioning-memory blog.

As the title says, what if you want to  combine the Create and Update from your CRUD code in one query; say you're performing an INSERT with a nested SELECT to insert more than one row, but there's a chance that one of these inserts breaks a unique key constraint somewhere and so you want to UPDATE in that case instead?

This is where INSERT ... ON DUPLICATE comes to the rescue.

Share this: