I am writing a stored procedure to update a products placements. I have a legacy database that contains a product table, a placements table, and a ProductsInPlacements table. The productsInPlacements table contains a productID as a FK, and a placementsID as a FK.
What is the best practice for updating this type of relationship? Currently I am updating, I first delete all the records from ProductsInPlacements that match a given product ID, then I loop calling an update procedure that take the productId and placementID and updates the ProductsInPlacements table.
This requires two stored procedures, one to delete all the records and another to update the table. Is this the best way?
I wrap everything in a transaction in case something bad happens during the update. I am using SQL server.
Any guidance would be greatly appreciated.