Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Unanswered: Merge statement help

    Can someone help me with the following merge statement (if thats the right way to go about what i want to do - see below), please?

    Note: image 1 is the Target table and image 2 is the Source table.

    Code:
    	merge into jSubRisk_Controls as target
    	using @ControlSubs as source
    	on target.ControlID = source.ctrlid
    	and target.SubRiskID = source.srid
    	WHEN MATCHED THEN
    		UPDATE
    		SET
    		ControlID = ctrlid
    		,SubRiskID = srid
    	when not matched by target then
    		insert	(ControlID, SubRiskID)
    		values (ctrlid,	srid)
    	when not matched by source then
    		delete;
    What i'm really trying to do is if the ctrlid in the source exists in the target then delete the relevant records in the target and repopulate with the source. And if it exists in target but not in the source then delete from the target.

    So using the attached examples rows 1,2 and 4 in image 1 should be deleted and the data form image 2 transferred.

    I hope that makes sense and thanks for looking/helping.
    Attached Thumbnails Attached Thumbnails jSubRisk_Controls data.jpg   jSubRisk_Controls source data.jpg  

  2. #2
    Join Date
    Oct 2005
    Posts
    22
    If this doesn't make sense let me know and i'll rephrase it.

    I really need to get this working.

    Thanks.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your UPDATE is superfluous, as it addresses the natural key you've already used to compare the two datasets. Simply put, you're performing an update on two fields when those two fields already match between the source and target.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2005
    Posts
    22
    If anyone's interested, this is how it was resolved...

    Code:
    declare @target table(SubRiskID int,ControlID int)
    insert into @target 
    select 5,21 union all
    select 6,21 union all
    select 6,22 union all
    select 7,21
    
    declare @source table(srid int,ctrlid int)
    insert into @source 
    select 5,21
    union all
    select 7,21 
    union all
    select 8,22
    
    select * from @target
    select * from @source
    
    declare @cid int
    set @cid = 21
    
    ; with MyTarget as (select * from @target where ControlID = @cid)
    
    merge MyTarget as target
        using @source as source
        on target.SubRiskID = source.srid
    
        when not matched by target then
            insert    (ControlID, SubRiskID)
            values (ctrlid,    srid)
        when not matched by source  then
            delete;
            
    select * from @target
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •