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.
Construct a query to update the customers' names and states in
customertarget table to match the names and states of customers having the same IDs in the
Enhance the query to insert data from
new_customer_stagetable into the
customertable 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);