Author |
Topic |
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-09 : 19:39:52
|
I have the table tblCollectionGameList CREATE TABLE dbo.tblCollectionGameList ( ListID smallint IDENTITY(0,1) NOT NULL, CollectionID smallint NOT NULL, IncludedSectionID smallint NOT NULL, CONSTRAINT PK_CollectionGameList_ListID PRIMARY KEY CLUSTERED (ListID ASC), CONSTRAINT FK_CollectionGameList_SectionInfo FOREIGN KEY (CollectionID) REFERENCES dbo.tblSectionInfo (SectionID), CONSTRAINT FK_CollectionGameList_SectionInfo2 FOREIGN KEY (IncludedSectionID) REFERENCES dbo.tblSectionInfo (SectionID) ) which I run the following MERGE command on after passing an XML set of values to from my website SELECT CAST(colx.query('data(CollectionID) ') AS varchar) AS CollectionID, CAST(colx.query('data(IncludedSectionID) ') AS varchar) AS IncludedSectionID INTO #TEMPtblCollectionGameList FROM @XMLTable.nodes('DocumentElement/XMLTable') AS Tabx(Colx) MERGE dbo.tblCollectionGameList AS t USING #TEMPtblCollectionGameList AS s ON (t.CollectionID = s.CollectionID AND t.IncludedSectionID = s.IncludedSectionID) WHEN NOT MATCHED BY TARGET THEN INSERT (CollectionID, IncludedSectionID) VALUES (s.CollectionID, s.IncludedSectionID) WHEN NOT MATCHED BY SOURCE THEN DELETE ; which is not working right. If I have an existing set of data in my table: ListID CollectionID IncludedSectionID 34 86 0 35 86 1 and I try to place new data from my web page for a collection with a different CollecitonID it is removing the old data and placing the new data in: ListID CollectionID IncludedSectionID 38 92 10 39 92 11 what is wrong with my MERGE code that is making it affect items outside the CollectionID that the webpage is passing for an update?-- 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-09 : 23:05:07
|
i think its this codeWHEN NOT MATCHED BY SOURCE THEN DELETEthis will cause it to delete all rows from target table which are not in source (in your case existing rows)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-10 : 08:51:54
|
Is there some way to make that statment only affect the data with a CollectionID of what ever is in #TEMPtblCollectionGameList? Maybe an if statment in it?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-10 : 09:15:49
|
Couple of things:First, is this part correct?ON (t.CollectionID = s.CollectionID AND t.CollectionID= s.CollectionID) May be you meant one or the other of the following?-- aON (t.CollectionID = s.CollectionID-- bON (t.CollectionID = s.CollectionID AND t.IncludedSectionID= s.IncludedSectionID) Second, there are 3 possible scenarios:1) the CollectionID (and IncludedSectionID combination if it is (b) ) in the #TEMPtblCollectionGameList is not in the tblCollectionGameList table. In that case you want to insert the new data. You are doing that correctly in the WHEN NOT MATCHED BY TARGET section.2) a CollectionID (and IncludedSectionID combination if it is (b) ) is not in the #TEMPtblCollectionGameList table, but is in the tblCollectionGameList. In this case, if I understood you correctly, you don't want to do anything at all. But, your WHEN NOT MATCHED BY SOURCE part is deleting such rows. So remove the section: WHEN NOT MATCHED BY SOURCE THEN DELETE 3) a WHEN MATCHED section. This is used if the join columns (in your case CollectionID (and IncludedSectionID combination if it is (b) )) match, but other columns may be different. Since you have only those two columns in your data, this does not apply at all.So, all you should have to do is simpley remove the WHEN NOT MATCHED BY SOURCE as Visakh had suggested.Also, people use MERGE statements when you want to do at least two of the 3 possible scenarios. So in your case, a simple insert may be sufficient. |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-10 : 12:04:31
|
Take a look at this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139264&SearchTerms=FTW,CTE |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-10 : 16:30:02
|
@sunitabeck, I corrected the first post. your combination (B) is what I am running I accedently copyed a test code instead of the orginal I was working off sorry. Your thoughts on scenarios 1 & 3 are correct but not for 2. tblCollectionGameList could contain many differnt CollecitonIDs since so far there are 7 different collections with each collection containing different Games (That is the point of that table, to hold the IDs of each game in a collection). The goal of this merge is to look at the incoming data from my webpage and go "Ok incoming CollecitonID is 89 so I need to now update tblCollectionGameList and remove any games for CollecitonID = 89 that was removed from the list, add any that where added to the list, and leave the ones alone that already have an entry that are still on the list". This is done fine but the problem is that if tblCollectionGameList has enterys for say CollecitonID = 30 then when CollectionID = 89 gets an update it dumps all rows for CollectionID = 30 and I do not want that to happen.@Lazerath Your link sounds like it might fix the problem but I don't understand it at all or how to apply it to my merge. I only learned the MERGE command 2 days ago and I nave never even heard of a CTE or see a WITH command inside SQL. Can you please elaberate a bit. Am I right in thinking that I should add between the SELECT INTO TEMP and MERGE commands something like:WITH dbo.tblCollectionGameList AS( SELECT ListID, CollectionID, IncludedSectionID FROM dbo.tblCollectionGameList AS CGL WHERE EXISTS ( SELECT CollectionID, IncludedSectionID FROM #TEMPtblCollectionGameList AS TempCGL WHERE CGL.CollectionID = TempCGL.CollectionID )) -- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-10 : 17:04:04
|
Exactly right, you need to add a CTE for your TARGET table where you filter it to the resultset you want to be affected by your WHEN NOT MATCHED BY SOURCE clause. Your EXISTS is perfect because it limits the TARGET to the collections contained within your temp table. I think the only thing you need to change is the CTE name -- make it unique and then use it in place of your target table within the MERGE. The INSERTS / UPDATES / DELETES will work through the CTE and still apply directly to the target table. |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-10-10 : 17:15:55
|
What do you mean by chaning the CTE name just add an AS clause after the last set of ")" or do you mean the table name after the WITH clause?-- 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-10 : 17:30:11
|
Testing out that CTE (changing WITH dbo.tblCollectionGameList AS to WITH tblCGL AS) and tested it, it is now working great thanks so much!-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-10 : 17:42:11
|
Glad to help! |
|
|
|