Merging data in tables

A sample statement shows how you can conditionally insert existing data in Hive tables using the ACID MERGE statement. Additional merge operations are mentioned.

The MERGE statement is based on ANSI-standard SQL.

Construct a query to update the customers' names and states in customer target table to match the names and states of customers having the same IDs in the new_customer_stage source table.
Enhance the query to insert data from new_customer_stage table into the customer table if none already exists.
Update or delete data using MERGE in a similar manner.
```
MERGE INTO customer USING (SELECT * FROM new_customer_stage) AS sub ON sub.id = customer.id 
WHEN MATCHED THEN UPDATE SET name = sub.name, state = sub.state 
WHEN NOT MATCHED THEN INSERT VALUES (sub.id, sub.name, sub.state);
```
note
You can map specific columns in the INSERT clause of the query instead of passing values (including null) for columns in the target table that do not have any data to insert. The unspecified columns in the INSERT clause are either mapped to null or use default constraints, if any.
For example, you can construct the INSERT clause as WHEN NOT MATCHED THEN INSERT VALUES (customer.id=sub.id, customer.name=sub.name, customer.state=sub.state) instead of WHEN NOT MATCHED THEN INSERT VALUES (sub.id, sub.name, 'null', sub.state).