I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from an API and is usually the same set of services, however every now and again the list may be updated and services become inactive and no longer show up in the results from the API call so these services should be updated in our database to reflect active as FALSE. Likewise is a new service comes onboard it should be added to the current list of available services. I had in mind to just drop all records and add the new ones fetched from the API however considering I have used the Ids for the existing services in other tables and I would still need to reference them I threw that idea out the window and now I am in a bit of a bind.
Services - (Currently in DB) Name | Id | Active Test1 3 true Test2 4 true Test3 5 true
I wanted to have a query or trigger of some sort to run when trying to insert duplicate data to table Services where constrained by the following:
- If an existing 'Name' is found skip the insert and move on to the next item in the array
- If a new 'Name' comes up not found in the DB add it as a new row. Example [Test4 5 true]
- If the newly fetched list from an API does not have one of the existing 'Name's, that is Test1,Test2 or Test3 update that existing row to set the Active column to false. So if the new list does not have Test3 the existing Test3 row would be updated to show Active as false.