Merge search for multiple conditions - SQL Server

I am trying to understand the merge search term and am facing the following problem.

Table 1

id  groupid    description
-------------------------    
1     10       Good
2     20       Better

      

Table2

id  groupid   description
-------------------------    
1    10       Very Good
1    20       Much Better

      

I intend to concatenate source (table1) into target (table2) with id present in both, but only with groupid = 20 present in target table.

This is what I write

Merge table1 source
Using table2 target ON (target.id = source.id AND target.groupid = 20)

When Matched
    Then update 
             set target.description = source.description

      

Expected Result

Table2

id  groupid   description
-------------------------   
1     10      Very Good
1     20      Good

      

But I'm not 100% sure of an ON clause (merge search condition) with multiple check conditions target.id = source.id and target.groupid = 20

. Is the result always predictable and in line with the expectation above under these few conditions? Or the predictability of the question here, and should I add target.groupId = 20

"when matching AND" to the condition?

+3


source to share


1 answer


It looks like your connection is wrong. You need to either join GROUPID or your details are wrong.

CONNECTION PER GROUP

create table #table1 (id int, groupid int, description varchar(64))
create table #table2 (id int, groupid int, description varchar(64))

insert into #table1 values
(1,10,'Good'),
(2,20,'Better')


insert into #table2 values
(1,10,'Very Good'),
(1,20,'Much Better')


Merge #table2 t
Using #table1 s 
ON (t.groupid = s.groupid AND t.groupid = 20)
When Matched
Then update 
set t.description = s.description;

select * from #table2

drop table #table2
drop table #table1

      



Otherwise, there is no way to match "better" from ID = 2

to the line where ID = 1

. This is contrary to the original join state in the column ID

.

BASED ON LIMITED EXPECTED OUTPUT

create table #table1 (id int, groupid int, description varchar(64))
create table #table2 (id int, groupid int, description varchar(64))

insert into #table1 values
(1,10,'Good'),
(2,20,'Better')


insert into #table2 values
(1,10,'Very Good'),
(1,20,'Much Better')


Merge #table2 t
Using #table1 s 
ON (t.id = s.id)         --you could also put the and t.groupid = 20 here...
When Matched and t.groupid = 20
Then update 
set t.description = s.description;

select * from #table2

drop table #table2
drop table #table1

      

+4


source







All Articles