MERGE INTO

Function

This command is used to query another table based on the join condition of a table or subquery. If UPDATE or DELETE is executed for the table matching the join condition, and INSERT is executed if the join condition is not met. This command completes the synchronization requiring only one full table scan, delivering higher efficiency than INSERT plus UPDATE.

Precautions

To merge partitioned tables, set spark.sql.forcePartitionPredicatesOnPartitionedTable.enabled to false.

Syntax

MERGE INTO [database_name.]table_name AS target_alias

USING (sub_query | tableIdentifier) AS source_alias

ON <merge_condition>

[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]

[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]

[ WHEN NOT MATCHED [ AND <condition> ] THEN <not_matched_action> ]

<merge_condition> =A equal bool condition

<matched_action> =

DELETE |

UPDATE SET * |

UPDATE SET column1 = expression1 [, column2 = expression2 ...]

<not_matched_action> =

INSERT * |

INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Parameter Description

Required Permissions

• SQL permissions

  Table 2 Permissions required for executing MERGE INTO

  Permission Description
  UPDATE permission on a table
  ALTER permission on a table
  DELETE permission on a table

• Fine-grained permissions: dli:table:update, dli:table:insertIntoTable, and dli:table:delete
• Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

Examples

• Update some fields.

  create table h0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1);
  insert into s0 values(1, 1, 's1', 1),(2, 2, 's2', 2);
  // Method 1
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set h0.id = s0.id, h0.comb = s0.comb, price = s0.price * 2;
  // Method 2
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set id = s0.id,
  name = s0.name,
  comb = s0.comb + h0.comb,
  price = s0.price + h0.price;

• Update and insert default fields.

  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into s0 values(1, 2, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  
  merge into h0 as target
  using (
  select id, comb, name, price, flag from s0
  ) source
  on target.id = source.id
  when matched then update set *
  when not matched then insert *;

• Update and delete data based on multiple conditions.

  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into h0 values(2, 2, 'h2', 1, false);
  insert into s0 values(1, 1, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  insert into s0 values(3, 3, 's3', 3, false);
  
  merge into h0
  using (
  select id, comb, name, price, flag from s0
  ) source
  on h0.id = source.id
  when matched and h0.flag = false then update set id = source.id, comb = h0.comb + source.comb, price = source.price * 2
  when matched and h0.flag = true then delete
  when not matched then insert *;

System Response

You can view the result in driver logs or on the client.