Alex P Alex P - 1 year ago 72
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download