TSQL updating/deleting from JunctionTable
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
(Company,Doors,Address,Address2,City,Region,Country,PostalCode,Lat,Long,Phone,Fax,Website,Description,Actualized,Verified,IMG1,IMG2,IMG3,IMG4,DateAdded)
VALUES
('[Company]','[Doors]','[Address]','[Address2]','[City]','[Region]','[Country]','[PostalCode]','[Lat]','[Long]','[Phone]','[Fax]','[Website]','[Description]','[Actualized]','[Verified]','[Image1]','[Image2]','[Image3]','[Image4]','[DateAdded]')
select scope_identity()
I'm storing the Identity of the above query in a token [ScopedLID] for the following query
DECLARE @CatIDStr VARCHAR(100) = '[CatID]',@CatID VARCHAR(100) = ''
WHILE LEN(@CatIDStr) > 0
BEGIN
IF CHARINDEX(',',@CatIDStr) = 0
BEGIN
SET @CatID = @CatIDStr
SET @CatIDStr = ''
END
ELSE
BEGIN
SELECT @CatID = SUBSTRING(@CatIDStr,0,CHARINDEX(',',@CatIDStr))
SELECT @CatIDStr=SUBSTRING(@CatIDStr,CHARINDEX(',',@CatIDStr)+1,LEN(@CatIDStr))
END
INSERT INTO BND_ListingJunction_testing (Junc_LID,Junc_CatID)
Values ('[ScopedLID]',@CatID)
END
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.