Update or Upsert?

Knowing this will change the way you manipulate database records.

These two methods can be used to update a database record, but it there’s a slight difference between how it updates the record.

UPDATE allows you to change the values of one or more columns in a set of records that meet specified conditions.

UPSERT on the other hand, is a database operation that combines the functionality of UPDATE and INSERT. It attempts to insert a new row into a table, and if the record already exists (based on a specified condition or unique key constraint), it updates the existing record instead of inserting a duplicate.
Also, The term “UPSERT” is a portmanteau of “UPDATE” and “INSERT”.

REAL WORLD APPLICATIONS

• Say you have an application where students manage their courses and some school related stuff, and a students requests to change their email for example.
  Since we’re very sure that a student must exist before they make a request to update their name, we simply use the UPDATE keyword.
  So, that means that it doesn’t check the database to see if the student exists.
  With this, we can say that when we want to modify the values of an existing record without inserting new records, you should use the UPDATE.

UPDATE students
SET email = newEmailAddress
WHERE id=1

• Now, If we want to update a course offered by a student, we can ue UPSERTS, because we would need to check if the course exists and insert a new entity if it doesnt.
  That would be a perfect case here, because a course might be newly introduced to students by a school, so.. in that case where we need to check if the course exists before updating it, we simply use UPSERT.

INSERT INTO student_courses (student_id, course_id, enrollment_status)
VALUES (123, 'Math101', 'Enrolled')
ON CONFLICT (student_id, course_id) DO UPDATE
SET enrollment_status = EXCLUDED.enrollment_status;

That’s almost all there is to knowing whether or not to use updates or upserts 🌈

But, using each of them heavily depends on your usecase, just as we saw in the real application.

If you have any more ideas about specific conditions where it would be idea to use any if them, please say them in the comments 🙏

Edit 1:

• If you’re curious about the progress I’m making so far, I just added some features to betastudents, e.g course-outlines, an admin portal for course representatives. please check it out, and feel free to ping me on twitter if there’s anything specific that you’d like me to build for you.
  Also, if you want something custom for students in your school, you can also send me an email so that we get to talk about it.

Edit 2:

• If you’re looking for a community to grow as a developer, then you should come hang with us at wecraftcode.
  We are a small group of people who leverage learning and programming through motivation, mentoring, and craftsmanship.