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?
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