Home FAQ Previous Next Demo

How UPDATE or INSERT a row into a target table in one statement?

Here is a task: There is bonuses table where some employees already have records. A decision was made to encourage employees of department 20 as follows: If employees already have bonus record and salary less than 50000 - add additional 2% of their salary to existing bonus. All others should have 3% of their salary.
How to make these changes in one statement?

The MERGE statement enables us to either UPDATE or INSERT a row into a target table in one statement.
See Demo 


MERGE INTO [table_name: bonuses T]
USING [query: (SELECT employee_id, salary
            FROM employee
           WHERE dept_no = 20) Q]
ON ([condition: T.employee_id = Q.employee_id])
WHEN MATCHED THEN [update_clause: UPDATE SET... WHERE Q.salary < 5000]
WHEN NOT MATCHED THEN [insert_clause: INSERT (...) VALUES (...)]
Home FAQ Previous Next Demo