How to REPLACE INTO using MERGE INTO for upsert in Delta Lake?

user_760807 0
Viewed 372

The recommended way of doing an upsert in a delta table is the following.

MERGE INTO users
USING updates
ON users.userId = updates.userId
WHEN MATCHED THEN
      UPDATE SET address = updates.addresses
WHEN NOT MATCHED THEN
      INSERT (userId, address) VALUES (updates.userId, updates.address)

Here updates is a table. My question is how can we do an upsert directly, that is, without using a source table. I would like to give the values myself directly.

In SQLite, we could simply do the following.

REPLACE INTO table(column_list)
VALUES(value_list);

Is there a simple way to do that for Delta tables?

delta-lake apache-spark-sql apache-spark upsert
1 Answers

A source table can be a subquery so the following should give you what you're after.

MERGE INTO events
USING (VALUES(...)) // round brackets are required to denote a subquery
ON false            // an artificial merge condition
WHEN NOT MATCHED THEN INSERT *
user_13053440
Related