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.