Answer-first summary for fast verification
Answer: MERGE INTO suppliers - USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *
The question requires merging data from two tables (suppliers and new_suppliers) while avoiding duplicates based on supplier_id. The MERGE INTO command (Option A) is specifically designed for this 'upsert' operation - it inserts new rows when no match is found and can update or leave unchanged when matches exist. The community discussion shows 100% consensus on A, confirming it's the correct Databricks SQL command for this scenario. Option B (COPY INTO) is for loading data from files, not merging tables. Option C (UPDATE) cannot insert new rows. Option D (INSERT INTO) would create duplicates rather than prevent them.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
An analyst needs to combine the data from the suppliers and new_suppliers tables. Some supplier_id values may exist in both tables, indicating those suppliers are already present in the suppliers table. For these matching rows, the data should remain unchanged.
Which command should be used to merge the two tables while ensuring that rows with duplicate supplier_id values are not added?
A
MERGE INTO suppliers -
USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *
B
COPY INTO suppliers -
USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *
C
UPDATE suppliers -
USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *
D
INSERT INTO suppliers -
USING new_suppliers - ON suppliers.supplier_id = new_suppliers.supplier_id WHEN NOT MATCHED THEN INSERT *
No comments yet.