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?
source to share
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
source to share