I'm having difficulty figuring out the correct way to manage the following scenario.
I have 3 tables Categories, Listing, Junction
I'm using the Junction table for many-to-many relationships between my Categories & Listing tables, as any given record inside my Listing table can be assigned 1 or many Categories from the Categories table.
I've figured out how to INSERT correctly into both. Now my predicament is Updating.
If a user wants to Update their Listing to new categories then my code needs to delete any existing records in the JunctionTable and replace with new ones. How can I write a SQL statement that accomplishes that?
These are my two insert statements that fire when i'm adding a listing.
INSERT INTO BND_Listing_testing
DECLARE @CatIDStr VARCHAR(100) = '[CatID]',@CatID VARCHAR(100) = ''
WHILE LEN(@CatIDStr) > 0
IF CHARINDEX(',',@CatIDStr) = 0
SET @CatID = @CatIDStr
SET @CatIDStr = ''
SELECT @CatID = SUBSTRING(@CatIDStr,0,CHARINDEX(',',@CatIDStr))
INSERT INTO BND_ListingJunction_testing (Junc_LID,Junc_CatID)
Use a transaction. Within that transaction first delete all rows from the junction table for the specified listing, and second add new rows for the selected combinations of listing and category.