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.

PhilHibbs
  • 539
  • 1
  • 7
  • 22

1 Answers1

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

flashsplat
  • 185
  • 1
  • 7