#TechForNewbies: Upsert

‘Upsert’ is a nifty little data operation which does what it says on the tin!

Have you ever been reading someone a list of RSVP’s, or groceries to buy, or anything — and they have already had half the information? Naturally, you only want to add the new RSVP responses, or new additions, to the list, before they go ahead and place their catering order or leave the house with their shopping bag — to avoid potentially disastrous duplication (overcatering on the gluten free garlic bread may just be the stressful straw which breaks the kitchen cupboard’s back).

You get the same issue with databases — sorting through existing data can be complex and resource-intensive, just like the extra thought you had to put into working out which of the RSVP’s you were reading were and were not newer than your friend’s list items.

Enter: upsert

Upsert = update or insert

It’s beautiful.

Upsert essentially tells the computer — if my record (e.g. Simon’s RSVP) is already there, update it to my new value. And, if it isn’t, then insert (add) it. Simples. You can apply extra logic and timestamps to ensure this only happens when your record is definitely more recent than the existing one.

So, how do you use upsert?

Upsert is used — and usable — in different ways depending on the database you are working with — so check the documentation! To help you get started, here are some examples of upsert in action: