Drupal 8 : How to use MERGE(INSERT+UPDATE) query with Syntax and Example

MERGE is the short hand Drupal database statement used to perform both INSERT and UPDATE functionality in same code.  It Will check whether record is already exits with given condition, yes then update based on condition otherwise insert a new record. The merge() is a function of Drupal 8 core class used for MERGING record based on condition records. Below is syntax and example of how to use MERGE statements in Drupal 8.

Syntax: 

\Drupal::database()->merge('TABLE_NAME')
	->key(array('FIELD_1' => CONDITION_VALUE))
	->insertFields(array(
		'FIELD_1' => VALUE_1,  // FIELD_1 value.
		'FIELD_2' => VALUE_2,  // FIELD_2 value.
		'FIELD_3' => VALUE_3,  // FIELD_3 value.
	))
	->updateFields(array(
		'FIELD_2' => NEW_VALUE_2,  // FIELD_2 NEW value.
		'FIELD_3' => NEW_VALUE_3,  // FIELD_3 NEW value.
	))->execute();

Example: 

// First Time
$empId = 'CE 003';
$empName = 'Kumar';
$empAge = 25;
 
\Drupal::database()->merge('employee')
	->key(array('employee_id' => $empId))
	->insertFields(array(
		'employee_id' => $empId,
		'employee_name' => $empName,
		'employee_age' => $empAge,
	))
	->updateFields(array(
		'employee_name' => $empName,
		'employee_age' => $empAge,
	))->execute();
 
// Second Time
$empId = 'CE 003';
$empName = 'Swathy';
$empAge = 20;
 
\Drupal::database()->merge('employee')
	->key(array('employee_id' => $empId))
	->insertFields(array(
		'employee_id' => $empId,
		'employee_name' => $empName,
		'employee_age' => $empAge,
	))
	->updateFields(array(
		'employee_name' => $empName,
		'employee_age' => $empAge,
	))->execute();

Initial Table be like:

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_AGE
CE 001 Mohan 20
CE 002 Dinesh 17

It insert new Record if not exist otherwise update values as given condition. First Time record Not Available so it insert as new Record:

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_AGE
CE 001 Mohan 20
CE 002 Dinesh 17
CE 002 Kumar 25

A Record with EMPLOYEE_ID of CE 003 So it update with new value:

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_AGE
CE 001 Mohan 20
CE 002 Dinesh 17
CE 002 Swathy 20
Category: