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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 MERGE statment removing data I don't want it too.

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 code

WHEN NOT MATCHED BY SOURCE
THEN
DELETE


this will cause it to delete all rows from target table which are not in source (in your case existing rows)


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

Go to Top of Page

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
Go to Top of Page

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?

-- a
ON (t.CollectionID = s.CollectionID

-- b
ON (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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-10 : 17:42:11
Glad to help!
Go to Top of Page
   

- Advertisement -