Hi, Akash. Here are the answers to your questions. And I think we can have a further investigation about merge into command in Carbondata. Q1. when there are multiple whenMatched conditions what happens? A1. The number of whenMatched and notMatched can be unlimited Since the Carbondata API for merge matched and unmatched clause are unlimited. However, the order of WHEN MATCHED and NOTMATCHED matters. The WHEN MATCHED clause should be placed before the NOTMATCHED. And once it matched the first matched/not matched clause, it will just begin to execute and ignore the others.
Q2. validations like when matched can have either update or delete, this validation A2. Yes, each when matched can only have either update/ delete once. And we need to validate the SQL Text, it the SQL Text do not match the rule of Merge Into, it will just return an SQL Parser Error back to the users. Q3. when the condition is not present for when matched, then how to update or delete complete data, example can be given, how the sql will look like A3. When there is not when matched clause, this merge into command can only use to act as INSERT INTO command when it meets the not matched clause. When all WHENMATCHED Clauses are missed, it begins to check the WHENNOTMATCHED Clause. Q4. how to update all columns of the target table from the source table and how the SQL will be and validations can be there A4. For this problem, I think it may only be available to match all the corresponding columns manually. MERGE INTO events USING updates ON events.eventId = updates.eventId WHEN MATCHED THEN UPDATE SET events.data = updates.data, events.name = updates.name Q5. validation for whenNot matched to have only insert A5. In matched clause: it can only have UPDATE/ DELETE execution In not matched clause: it can only execute INSERT INTO Q6. what happens when the columns do not match from source and target A6. I think it will do nothing Q7. how to insert complete data. A7. When it do not match any match clause, it will run to the when not matched clause. I think there can be two solutions, on the solution is to insert * The SQL look like this MERGE INTO logs USING newDedupedLogs ON logs.uniqueId = newDedupedLogs.uniqueId WHEN NOT MATCHED THEN INSERT * And the other solution is to match the columns one by one. We can further discuss these rules and limits of MERGE INTO since the code is still being implemented. Thanks Brooks Li -- Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
