CODEWITHSUNDEEP
databricksazure-databricksdatabricks-sql
Patterson
Patterson

Reputation: 2821

Databricks Merge on multiple columns

The following Databricks SQL query will allow me to merge on a single column from a single database

 CREATE OR REPLACE TEMP VIEW rs AS
    SELECT *, ROW_NUMBER() OVER (ORDER BY rand()) AS seq
    FROM basef1area.drivers;
    
    MERGE INTO basef1area.races AS t
    USING rs
    ON t.race_ID = rs.seq
    WHEN MATCHED THEN
      UPDATE SET t.reject_reason = rs.driver_ID;

Can someone let me know how to modify the query that will allow to use another database table (basef1area.circuits) to merge on an additional column, such that the UPDATE SET clause would something like:

UPDATE SET t.reject_reason = rs.driver_ID;
UPDATE SET t.race_name= rs.driver_ref;

Upvotes: 0

Views: 229

Answers (1)

Chen Hirsh
Chen Hirsh

Reputation: 1400

To update multiple columns on merge:

MERGE INTO basef1area.races AS t
    USING rs
    ON t.race_ID = rs.seq
    WHEN MATCHED THEN
      UPDATE SET t.reject_reason = rs.driver_ID,
                 t.race_name= rs.driver_ref;

Or, if you want to update all fields:

MERGE INTO basef1area.races AS t
    USING rs
    ON t.race_ID = rs.seq
    WHEN MATCHED THEN
      UPDATE SET *

Upvotes: 0

Related Questions