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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 MERGE statment removing data I don't want it too.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 10/09/2012 :  19:39:52  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

Edited by - Eagle_f90 on 10/10/2012 16:16:46

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/09/2012 :  23:05:07  Show Profile  Reply with Quote
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

USA
419 Posts

Posted - 10/10/2012 :  08:51:54  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  09:15:49  Show Profile  Reply with Quote
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

USA
319 Posts

Posted - 10/10/2012 :  12:04:31  Show Profile  Reply with Quote
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

USA
419 Posts

Posted - 10/10/2012 :  16:30:02  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
@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

USA
319 Posts

Posted - 10/10/2012 :  17:04:04  Show Profile  Reply with Quote
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

USA
419 Posts

Posted - 10/10/2012 :  17:15:55  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

Edited by - Eagle_f90 on 10/10/2012 17:17:00
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 10/10/2012 :  17:30:11  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

USA
319 Posts

Posted - 10/10/2012 :  17:42:11  Show Profile  Reply with Quote
Glad to help!
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