Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-10-03 : 16:30:24
|
| 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
1974 Posts |
Posted - 2012-10-03 : 16:55:40
|
| I'm not sure I understand the problem, but can you use a MERGE statement?-Chad |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-03 : 17:09:12
|
| 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 |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-03 : 17:21:05
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 17:35:09
|
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 thatyou can use UNMATCHED BY SOURCE and UNMATCHED BY TARGET conditions for achieving this.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Best way to update a linking table?