How to update the sql table efficiently

I have a requirement where I need to update the delta records in my table. Here is the complete description:

Sample Environment Details:

a. I have a student table like capabilities which has the below structure:

    Id    Name   Capability
    ==  =====  =======
    1     xxx       Athletic
    2     xxx       Cricket Player
    3     xxx       Football Player
    4     yyy       Musician

b. The table can have multiple entries for each student, where each entry represents a unique capability of a student.

Problem Statement:

The update to the above table comes from a REST request where we get the complete capabilities list such as
xxx -> Athletic, Cricket Player, Football Player, Musician, SINGER
xxx -> Cricket Player, Football Player

So, if we see above 2 requests,
in req 1, the capability SINGER is added to the student xxx.
In req 2, the capabilities Athletic and Musician got removed.

Now in order to update the table, I am deleting all the rows corresponding to student xxx.
And adding the new rows for each capability in the request.

I am not feeling this approach is an efficient one.

Can someone let me know the best way to handle the above update.

I'm not 100% sure I understand what you trying to do, and also your example doesn't seem logic.

  • Please explain "REST request". Is this a table? Whats the DDL? Is it just one long string?
  • In req1 Musician and SINGER are missing from xxx (since Musician is a capability of yyy)
  • In req2 Athletic, SINGER and Musician are missing (since SINGER and Musician were added in req1)
  • Since you in this case have 2 requests, how do you know the order (which come first/last)