Merge statement

sathish

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain3y ago

Accepted 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 Paul3y ago

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 Paul3y ago

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;
    }
}