Toad World® Forums

Merge statement generating multiple inserts on the same key

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

)
values
(
SY_SC_LINK_USER_GRP_ID.NextVal,
VLBUG.SY_SC_USER_ID ,
VLBUG.SY_SC_GROUP_ID ,
‘BEN’ ,
VLBUG.EE_EFFECTIVE ,
VLBUG.EE_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:

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 SY_SC_User_ID || SY_SC_Group_ID,SY_SC_User_ID,

SY_SC_Group_ID, EE_Effective, EE_Expires

) 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

)

values

(

SY_SC_LINK_USER_GRP_ID.NextVal,

VLBUG.SY_SC_USER_ID ,

VLBUG.SY_SC_GROUP_ID ,

‘BEN’ ,

VLBUG.EE_EFFECTIVE ,

VLBUG.EE_EXPIRES

);

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?