To illustrate, consider the following example source and target tables and data. Target(Employee ID int, Employee Name varchar(10), CONSTRAINT Target_PK PRIMARY KEY(Employee ID)); CREATE TABLE dbo.Source(Employee ID int, Employee Name varchar(10), CONSTRAINT Source_PK PRIMARY KEY(Employee ID)); GO INSERT dbo.For example We want to change the name of employee no 102 from ‘Sami’ to ‘Mohd Sami’ and to raise the salary by 10%. update emp set name=’Mohd Sami’, sal=sal (sal*10/100) where empno=102; Now we want to raise the salary of all employees by 5%.
In the following example, the filtering conditions are applied to the WHEN NOT MATCHED BY TARGET and the WHEN NOT MATCHED BY SOURCE. However, doing so can cause unexpected and incorrect results.update emp set name=upper(name); Suppose We have a student table with the following structure.Now to compute total which is sum of Maths, Phy and Chem and average.Rows in the source are matched with rows in the target based on the join predicate specified in the ON clause. One insert, update, or delete operation is performed per input row.Depending on the WHEN clauses specified in the statement, the input row might be any one of the following: The combination of WHEN clauses specified in the MERGE statement determines the join type that is implemented by the query processor and affects the resulting input stream.