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.
  1. Construct a query to update the customers' names and states in customer table to match the names and states of customers having the same IDs in the new_customer_stage table.
  2. 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) sub ON sub.id = customer.id 
    WHEN MATCHED THEN UPDATE SET name = sub.name, state = sub.new_state 
    WHEN NOT MATCHED THEN INSERT VALUES (sub.id, sub.name, sub.state);