Merge statement when not matched insert

Question

Merge statement when not matched insert

sqlfriend

SSC Guru

Points: 52769
More actions
July 27, 2017 at 5:21 pm

#402372

I am using merge statement to update and insert records to a target table.
My question is when I use when not matched (by target) , can I add additional conditions?
For example
MERGE INTO TargetTable T
USING (select .. from table a inner join table b...on .) S
WHEN MATCHED THEN
UPDATE....
WHEN NOT MATCHED and ( S.apcode is not null or S.IBcode is not null)
THEN INERT .......
I am wondering if I can add the condition: ( S.apcode is not null or S.IBcode is not null) after when not matched.
It seems not.
How can I achieve this: I would like to update if there is a match, and insert if some of the source column is not null.
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

If the criteria for insert is differs from the update criteria you need to do an update then an insert, otherwise you can apply the criteria to the source using a subquery or a CTE:
MERGE INTO dbo.ADDR trgt USING ( SELECT CITY_NAME , LINE_1_ADDR , LINE_2_ADDR , STATE_CODE , COUNTY_CODE , ID , CREATED_BY , UPDATED_BY , CREATED_ON , UPDATED_ON , REGN_CODE , ZIP_CODE_SUFFIX , ZIP_CODE_NUM , LAT_COORD_NUM , LONGTDE_COORD_NUM , COUNTRY_CODE FROM Staging.dbo.ADDR WHERE UPDATED_ON > (SELECT Max(UPDATED_ON) FROM dbo.ADDR a) ) src ON trgt.ID = src.ID WHEN MATCHED THEN UPDATE SET CITY_NAME = src.CITY_NAME ,LINE_1_ADDR = src.LINE_1_ADDR ,LINE_2_ADDR = src.LINE_2_ADDR ,STATE_CODE = src.STATE_CODE ,COUNTY_CODE = src.COUNTY_CODE ,ID = src.ID ,CREATED_BY = src.CREATED_BY ,UPDATED_BY = src.UPDATED_BY ,CREATED_ON = src.CREATED_ON ,UPDATED_ON = src.UPDATED_ON ,REGN_CODE = src.REGN_CODE ,ZIP_CODE_SUFFIX = src.ZIP_CODE_SUFFIX ,ZIP_CODE_NUM = src.ZIP_CODE_NUM ,LAT_COORD_NUM = src.LAT_COORD_NUM ,LONGTDE_COORD_NUM = src.LONGTDE_COORD_NUM ,COUNTRY_CODE = src.COUNTRY_CODE WHEN NOT MATCHED THEN INSERT ( CITY_NAME ,LINE_1_ADDR ,LINE_2_ADDR ,STATE_CODE ,COUNTY_CODE ,ID ,CREATED_BY ,UPDATED_BY ,CREATED_ON ,UPDATED_ON ,REGN_CODE ,ZIP_CODE_SUFFIX ,ZIP_CODE_NUM ,LAT_COORD_NUM ,LONGTDE_COORD_NUM ,COUNTRY_CODE ) VALUES ( src.CITY_NAME ,src.LINE_1_ADDR ,src.LINE_2_ADDR ,src.STATE_CODE ,src.COUNTY_CODE ,src.ID ,src.CREATED_BY ,src.UPDATED_BY ,src.CREATED_ON ,src.UPDATED_ON ,src.REGN_CODE ,src.ZIP_CODE_SUFFIX ,src.ZIP_CODE_NUM ,src.LAT_COORD_NUM ,src.LONGTDE_COORD_NUM ,src.COUNTRY_CODE );
OR
WITH src AS ( SELECT CITY_NAME , LINE_1_ADDR , LINE_2_ADDR , STATE_CODE , COUNTY_CODE , ID , CREATED_BY , UPDATED_BY , CREATED_ON , UPDATED_ON , REGN_CODE , ZIP_CODE_SUFFIX , ZIP_CODE_NUM , LAT_COORD_NUM , LONGTDE_COORD_NUM , COUNTRY_CODE FROM Staging.dbo.ADDR WHERE UPDATED_ON > (SELECT Max(UPDATED_ON) FROM dbo.ADDR a) ) MERGE INTO dbo.ADDR trgt USING src ON trgt.ID = src.ID WHEN MATCHED THEN UPDATE SET CITY_NAME = src.CITY_NAME ,LINE_1_ADDR = src.LINE_1_ADDR ,LINE_2_ADDR = src.LINE_2_ADDR ,STATE_CODE = src.STATE_CODE ,COUNTY_CODE = src.COUNTY_CODE ,ID = src.ID ,CREATED_BY = src.CREATED_BY ,UPDATED_BY = src.UPDATED_BY ,CREATED_ON = src.CREATED_ON ,UPDATED_ON = src.UPDATED_ON ,REGN_CODE = src.REGN_CODE ,ZIP_CODE_SUFFIX = src.ZIP_CODE_SUFFIX ,ZIP_CODE_NUM = src.ZIP_CODE_NUM ,LAT_COORD_NUM = src.LAT_COORD_NUM ,LONGTDE_COORD_NUM = src.LONGTDE_COORD_NUM ,COUNTRY_CODE = src.COUNTRY_CODE WHEN NOT MATCHED THEN INSERT ( CITY_NAME ,LINE_1_ADDR ,LINE_2_ADDR ,STATE_CODE ,COUNTY_CODE ,ID ,CREATED_BY ,UPDATED_BY ,CREATED_ON ,UPDATED_ON ,REGN_CODE ,ZIP_CODE_SUFFIX ,ZIP_CODE_NUM ,LAT_COORD_NUM ,LONGTDE_COORD_NUM ,COUNTRY_CODE ) VALUES ( src.CITY_NAME ,src.LINE_1_ADDR ,src.LINE_2_ADDR ,src.STATE_CODE ,src.COUNTY_CODE ,src.ID ,src.CREATED_BY ,src.UPDATED_BY ,src.CREATED_ON ,src.UPDATED_ON ,src.REGN_CODE ,src.ZIP_CODE_SUFFIX ,src.ZIP_CODE_NUM ,src.LAT_COORD_NUM ,src.LONGTDE_COORD_NUM ,src.COUNTRY_CODE );

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thank you, I am not ver clear, it looks the same to me the first and the second example

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

sqlfriends - Thursday, July 27, 2017 5:21 PM
MERGE INTO TargetTable T
USING (select .. from table a inner join table b...on .) S
WHEN MATCHED THEN
UPDATE....
WHEN NOT MATCHED and ( S.apcode is not null or S.IBcode is not null)
THEN INERT .......

you should be able to add extra conditions to the WHEN clauses of MERGE, I've done it before. What problem did you have when you added the "and (S.apcode is not null or S.IBcode is not null)" ?

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thank you. Just checked, you are right, it be added in T_SQL .

I thought this is the same in in oracle PL_SQL.

I am in fact working on a script that use merge statement to update a oracle database. It seems I cannot add additional conditions after when not matched...
or when matched in pl_sql.
For when matched then update I can use a where statement to limit the update records,
But when not matched then insert I cannot either add a condition nor add a where clause

Any suggestions?

Thanks,

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

I'm afraid my PL/SQL is a bit rusty, I haven't really worked with it since Oracle 9. I did find an article though that mentions in 10G they added something called "conditional dml"
http://www.oracle-developer.net/display.php?id=310#0ca14

the WHERE condition seems to go after the INSERT... VALUES... clauses.

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

Thank you, it is good article.
It seems it is allowed to add a where statement to when not matched then insert........ where....

That's what I did. But I found there are records inserted that are all nulls.
Let me dig more to see if I can find why.

Thanks