Alex P Alex P - 13 days ago 5
SQL Question

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

Answer

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.

Comments