SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Best way to update a linking table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 10/03/2012 :  16:30:24  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
I have a conundrum on how to best do something in SQL. I have two tables’ tblSectionInfo and tblCollectionGameList. tblCollectionGameList is used to contain a list of games that are included in a collection release and this is done using 3 columns CollectionListID (PK, IDENTITY) CollectionID (FK, Referances SectionID in tblSectionInfo), and IncludedGame (FK, Referances SectionID in tblSectionInfo). The though is to insert one row for each game that is included in a collection, the problem comes in update this list. The update is done by an ASP.NET webpage where the user can place a checkmark next to all the Games that are included in the collection. Doing the initial insert is easy where I am having problems at is updating the list. I am not sure the best way to update the table, I was thinking just deleting every entry in the table for the CollectionID but that could cause me to have to use a large int for the PK and seemed to be a bad way of doing this. Does anyone know a good way I could take the list of checked games from the ASP.NET page and maybe compare it somehow the existing info, delete what is not in the new list and add any new items from that list into the table?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/03/2012 :  16:55:40  Show Profile  Visit chadmat's Homepage  Reply with Quote
I'm not sure I understand the problem, but can you use a MERGE statement?


-Chad
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 10/03/2012 :  17:09:12  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
Sorry I am not very good at explaining things some times. The problem is that tblCollectionGameList Has one row in it for every game in a collection. So if a collection has 3 games there are 3 entries into the table. I need to figure out a good way to update this table so if the 3 enties where Game1, Game2, and Game3 and I need it so it had 4 entries of Game1, Game3, Game 4, Game5. I can drop Game2 and add Game4 and Game5. The new list would come from an ASP.NET page not another table so I don't think MERGE would work.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 10/03/2012 :  17:21:05  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
Ok maybey our onto something. Not sure if this would work or really how to do it (never worked with MERGE before). Could i take the the list from the asp.net page and insert into temp table, user the MERGE to comaire the two tables and INSERT any columns not found in tblCollectionGameList that are in the TEMP and then DELETE any tables in tblCollectionGameList that are not in TEMP?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/03/2012 :  17:35:09  Show Profile  Reply with Quote
quote:
Originally posted by Eagle_f90

Ok maybey our onto something. Not sure if this would work or really how to do it (never worked with MERGE before). Could i take the the list from the asp.net page and insert into temp table, user the MERGE to comaire the two tables and INSERT any columns not found in tblCollectionGameList that are in the TEMP and then DELETE any tables in tblCollectionGameList that are not in TEMP?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia


you could do that

you can use UNMATCHED BY SOURCE and UNMATCHED BY TARGET conditions for achieving this.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000