Coloplast Coloplast - 7 months ago 16
SQL Question

SQL MERGE statement deleting exceptions

The MERGE statement works except for the last section "WHEN NOT MATCHED BY SOURCE"
Here is the statement:

MERGE INTO [SQDKRTV96].[MERGETEST].[dbo].[users] T
USING [MERGETEST].[dbo].[PS_VIEW] S
ON S.[NAME_AC] = T.[Initialer]
WHEN MATCHED AND (
T.[Løn nummer] <> S.[EMPLID] OR
T.[Fornavn] <> S.[FIRST_NAME] OR
T.[Efternavn] <> S.[LAST_NAME]
)
THEN UPDATE SET
T.[Løn nummer] = S.[EMPLID],
T.[Fornavn] = S.[FIRST_NAME],
T.[Efternavn] = S.[LAST_NAME]
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[Løn Nummer],
[Initialer],
[Fornavn],
[Efternavn]
) VALUES (
S.[EMPLID],
S.[NAME_AC],
S.[FIRST_NAME],
S.[LAST_NAME]
)
WHEN NOT MATCHED BY SOURCE AND (T.[Fornavn] NOT LIKE 'key%' OR T.[Fornavn] NOT LIKE 'guest%' OR T.[Fornavn] NOT LIKE 'udlån%' OR T.[Fornavn] NOT LIKE 'vikar%' OR T.[Fornavn] NOT LIKE 'test%')
THEN DELETE;


I want the MERGE statement not to delete records with the NOT LIKE keyword defined for them.
But this MERGE statements keeps deleting them, why?
How can I accomplish that they stay?
Thanks in advance.

Answer

The predicate T.[Fornavn] NOT LIKE 'key%' OR T.[Fornavn] NOT LIKE 'guest%' OR T.[Fornavn] NOT LIKE 'udlån%' OR T.[Fornavn] NOT LIKE 'vikar%' OR T.[Fornavn] NOT LIKE 'test%' will not filter any rows because each side of the OR is mutually exclusive. For example, any [Fornavn] starting with "key" will not start with "guest". Thus even rows with a [Fornavn] starting with "key" will be brought back despite your NOT LIKE. Presumably you want to use AND rather than OR in this instance.