0

Here's a good example of a MERGE statement:

MERGE INTO target AS T 
USING source AS S
ON T.ID = S.ID and T.tran_time = S.tran_time 
WHEN MATCHED UPDATE SET quantity = S.quantity
WHEN MATCHED AND S.quantity IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.quantity, S.tran_time);

When doing the INSERT, does it have to exactly match the structure of the target table, or can a column list be somehow be specified like in a simple INSERT statement? I don't like hard coding value lists that have to match the order of the columns in the table, I like my statements to be column order independent.

1

Every example I've seen includes all of the columns. I also found this note about updates (though nothing specific to insert):

Note that you cannot update only a part of a complex structure field. For example, suppose you have a structure stored as one field in a Hive table: {"name":"Johnson","surname":"Fall","age":23,"gender":"MALE"} You cannot update only the age field in the structure. You can only replace all values of the structure with new ones.

From: https://mapr.com/docs/61/Hive/MERGE_Statement_with_MapR-DB-JSON-tables.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.