I am trying to merge rows from a view (VW_Link_Ben_Usr_Grp) that contains duplicate user and group membership combinations into a table (SY_SC_Link_Usr_Grp) with the result of a particular user / group membership combination only occurring once. Below is the merge statement I came up with, but it produces an error saying missing right parenthesis. I tried this same merge “Using” VW_Link_Ben_Usr_Grp instead of the select distinct, but it resulted in a row for every user /group combination including duplicates.
Thoughts?
Merge Into
SY_SC_LINK_USER_GRP
Using
(Select Distinct(SY_SC_User_ID,SY_SC_Group_ID) US_Group,
SY_SC_User_ID,
SY_SC_Group_ID,
EE_Effective,
EE_Expires
From VW_Link_Ben_Usr_Grp
Group By Distinct(SY_SC_Group_ID,SY_SC_Group_ID)
) VLBUG
On
(
VLBUG.SY_SC_User_ID = SY_SC_LINK_USER_GRP.SY_SC_User_ID And
VLBUG.SY_SC_Group_ID = SY_SC_LINK_USER_GRP.SY_SC_Group_ID
)
When Matched Then
Update Set UG_Expires = VLBUG.EE_Expires
When Not Matched Then
Insert
(
SY_SC_LINK_USER_GRP_ID,
SY_SC_USER_ID ,
SY_SC_GROUP_ID ,
UG_ASSIGNMENT_TYPE ,
UG_EFFECTIVE ,
UG_EXPIRES
I solved it. The solution is to concatenate the columns in the DIstinct function instead of separating with a comma, and eliminate the “Distinct” in the group by, simply listing
SY_SC_User_ID || SY_SC_Group_ID. So, the corrected version looks like:
I do still wonder why the original merge creates duplicate rows. It seems to me if a merge statement does a match on a repeated set of values against a target table, the first time it would insert when not found, and thereafter it should find the row in the target table and then do an update?