Merge statement

Question

Hi,

I have to update the column based on match and not match conditions
But Merge supports update only for Matched and not supported in not matched conditions

It shows error as 'Update not allowed when not mached'

Kindly share your solutions

Thanks

Rajkiran Swain · Answer

MERGE INTO your_table t USING (SELECT id, col1, col2 FROM your_source_table) s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.col1 = s.col1 WHEN NOT MATCHED THEN INSERT (id, col1, col2) VALUES (s.id, s.col1, s.col2); UPDATE your_table t SET col1 = 'default_value' WHERE NOT EXISTS (SELECT 1 FROM your_source_table s WHERE t.id = s.id);

Tuhin Paul · Answer

Part 2 MERGE INTO your_table t USING ( SELECT id, col1, col2 FROM your_source_table ) s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.col1 = s.col1, t.col2 = s.col2 WHEN NOT MATCHED THEN INSERT (id, col1, col2) VALUES (s.id, s.col1, s.col2); This MERGE statement will update the matching records and insert the non-matching records in a single query, which is more efficient than running two separate queries.

Tuhin Paul · Answer

The aliases used in the USING clause and INSERT statement should be more descriptive than s and t. This will make the code easier to read and understand. It is important to wrap both the MERGE statement and the UPDATE statement in a transaction to ensure that the two statements are executed atomically. If the default value used in the UPDATE statement ('default_value') is going to be changed frequently, it would be better to use a parameter instead of hard-coding the value in the SQL statement. Sharing a code based on the above: using (var transaction = _context.Database.BeginTransaction()) { try { _context.Database.ExecuteSqlRaw( @"MERGE INTO your_table t USING (SELECT id, col1, col2 FROM your_source_table) s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.col1 = s.col1 WHEN NOT MATCHED THEN INSERT (id, col1, col2) VALUES (s.id, s.col1, s.col2);"); _context.Database.ExecuteSqlRaw( @"UPDATE your_table t SET col1 = @defaultValue WHERE NOT EXISTS (SELECT 1 FROM your_source_table s WHERE t.id = s.id);", new SqlParameter("@defaultValue", "default_value")); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); // Log the exception or throw a custom exception // depending on your error handling strategy throw; } }