MERGE INTO

Aplica-se a: SQL do Databricks Runtime do Databricks

Mescla um conjunto de atualizações, inserções e exclusões com base em uma tabela de origem em uma tabela Delta de destino.

Há suporte apenas para essa instrução em tabelas do Delta Lake.

Esta página contém detalhes sobre como usar a sintaxe correta com o comando MERGE. Consulte Upsert em uma tabela do Delta Lake usando mesclagem para obter mais diretrizes sobre como usar as operações MERGE para gerenciar seus dados.

Sintaxe

MERGE [ WITH SCHEMA EVOLUTION ] INTO target_table_name [target_alias]
   USING source_table_reference [source_alias]
   ON merge_condition
   { WHEN MATCHED [ AND matched_condition ] THEN matched_action |
     WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition ] THEN not_matched_action |
     WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN not_matched_by_source_action } [...]

matched_action
 { DELETE |
   UPDATE SET * |
   UPDATE SET { column = { expr | DEFAULT } } [, ...] }

not_matched_action
 { INSERT * |
   INSERT (column1 [, ...] ) VALUES ( expr | DEFAULT ] [, ...] )

not_matched_by_source_action
 { DELETE |
   UPDATE SET { column = { expr | DEFAULT } } [, ...] }

Parâmetros

• WITH SCHEMA EVOLUTION

  Aplica-se a: Databricks Runtime 15.2 e versões posteriores

  Habilita a evolução automática do esquema para esta operação MERGE. Quando habilitado, o esquema da tabela Delta de destino é atualizado automaticamente para que ele corresponda ao esquema da tabela de origem.

• target_table_name

  Um Nome de tabela que identifica a tabela que está sendo modificada. A tabela referenciada deve ser uma tabela Delta.

  O nome não deve incluir uma especificação de opções.

  A tabela não deve ser uma tabela estrangeira.

• target_alias

  Um Alias de tabela para a tabela de destino. O alias não deve incluir uma lista de colunas.

• source_table_reference

  Um Nome de tabela que identifica a tabela de origem a ser mesclada na tabela de destino.

• source_alias

  Um Alias de tabela para a tabela de origem. O alias não deve incluir uma lista de colunas.

• ON merge_condition

  Como as linhas de uma relação são combinadas com as linhas de outra relação. Uma expressão com um tipo de retorno BOLEANO.

• WHEN MATCHED [ AND matched_condition]

  WHEN MATCHED cláusulas são executadas quando uma linha de origem corresponde a uma linha da tabela de destino com base em merge_condition e match_condition opcional.

• matched_action

  • DELETE

    Exclui a linha da tabela de destino correspondente.

    As diversas correspondências são permitidas quando as correspondências são excluídas definitivamente. Uma exclusão definitiva não é ambígua, mesmo que haja várias correspondências.

  • UPDATE

    Atualiza a linha da tabela de destino correspondente.

    Para atualizar todas as colunas da tabela Delta de destino com as colunas correspondentes do conjunto de dados de origem, use UPDATE SET *. Isso é equivalente a UPDATE SET col1 = source.col1 [, col2 = source.col2 ...] para todas as colunas da tabela Delta de destino. Portanto, essa ação pressupõe que a tabela de origem tenha as mesmas colunas que as da tabela de destino, caso contrário, a consulta gerará um erro de análise.

    Observação

    Esse comportamento muda quando a migração automática de esquema está habilitada. Confira Evolução automática de esquema para mesclagem do Delta Lake para obter detalhes.

    Aplica-se a: Databricks SQL Databricks Runtime 11.3 LTS e versões posteriores

    Você pode especificar DEFAULT como expr para atualizar explicitamente a coluna para seu valor padrão.

  Se houver várias cláusulas WHEN MATCHED, elas serão avaliadas na ordem em que são especificadas. Cada cláusula WHEN MATCHED, exceto a última, deve ter um matched_condition. Caso contrário, a consulta retornará um erro NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION.

  Se nenhuma das condições WHEN MATCHED for avaliada como verdadeira para um par de linhas de origem e de destino que corresponde à merge_condition, logo a linha de destino ficará inalterada.

• WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition]

  WHEN NOT MATCHED as cláusulas inserem uma linha quando uma linha de origem não corresponde a nenhuma linha de destino com base em merge_condition e not_matched_condition opcional.

  Aplica-se a: Databricks SQL Databricks Runtime 12.2 LTS e versões posteriores

  WHEN NOT MATCHED BY TARGET pode ser usado como um alias para WHEN NOT MATCHED.

  not_matched_condition deve ser uma expressão booleana.

  • INSERT *

    Inserir todas as colunas da tabela Delta de destino com as colunas correspondentes da base de dados de origem. Isso é equivalente a INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...]) para todas as colunas da tabela Delta de destino. Essa ação exige que a tabela de origem tenha as mesmas colunas que aquelas na tabela de destino.

    Observação

    Esse comportamento muda quando a migração automática de esquema está habilitada. Confira Evolução automática de esquema para mesclagem do Delta Lake para obter detalhes.

  • INSERT ( ... ) VALUES ( ... )

    A nova linha é gerada com base na coluna especificada e nas expressões correspondentes. Todas as colunas na tabela de destino não precisam ser especificadas. Para colunas de destino não especificadas, o padrão da coluna é inserido ou NULL caso não exista nenhuma.

    Aplica-se a: Databricks SQL Databricks Runtime 11.3 LTS e versões posteriores

    Você pode especificar DEFAULT como uma expressão para inserir explicitamente o padrão de coluna para uma coluna de destino.

  Se houver várias cláusulas WHEN NOT MATCHED, elas serão avaliadas na ordem em que são especificadas. Todas as cláusulas WHEN NOT MATCHED, exceto a última, devem ter not_matched_conditions. Caso contrário, a consulta retornará um erro NON_LAST_NOT_MATCHED_CLAUSE_OMIT_CONDITION.

• WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition]

  Aplica-se a: Databricks SQL Databricks Runtime 12.2 LTS e versões posteriores

  As cláusulas WHEN NOT MATCHED BY SOURCE são executadas quando uma linha de destino não corresponde a nenhuma linha na tabela de origem com base no merge_condition e o not_match_by_source_condition opcional é avaliado como true.

  not_matched_by_source_condition deve ser uma expressão booliana que faz referência apenas a colunas da tabela de destino.

• not_matched_by_source_action

  • DELETE

    Exclui a linha da tabela de destino.

  • UPDATE

    Atualiza a linha da tabela de destino. expr só pode fazer referência a colunas da tabela de destino; caso contrário, a consulta gerará um erro de análise.

    Aplica-se a: Databricks SQL Databricks Runtime 11.3 LTS e versões posteriores

    Você pode especificar DEFAULT como expr para atualizar explicitamente a coluna para seu valor padrão.

  Importante

  A adição de uma cláusula WHEN NOT MATCHED BY SOURCE para atualizar ou excluir linhas de destino quando a merge_condition é avaliada como false poderá levar a um grande número de linhas de destino sendo modificadas. Para obter o melhor desempenho, aplique not_matched_by_source_conditions para limitar o número de linhas de destino atualizadas ou excluídas.

  Se houver várias WHEN NOT MATCHED BY SOURCE clauses, elas serão avaliadas na ordem em que são especificadas. Cada cláusula WHEN NOT MATCHED BY SOURCE, exceto a última, deve ter um not_matched_by_source_condition. Caso contrário, a consulta retornará um erro NON_LAST_NOT_MATCHED_BY_SOURCE_CLAUSE_OMIT_CONDITION.

  Se nenhuma das condições WHEN NOT MATCHED BY SOURCE for avaliada como true em uma linha de destino que não corresponda a nenhuma linha na tabela de origem com base no merge_condition, a linha de destino será deixada inalterada.

Importante

MERGE As operações falharão com um erro DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE se mais de uma linha na tabela de origem corresponder à mesma linha na tabela de destino com base nas condições especificadas nas ON cláusulas AND WHEN MATCHED . De acordo com a semântica SQL de mesclagem, esse tipo de operação de atualização é ambíguo porque não está claro qual linha de origem deve ser usada para atualizar a linha de destino correspondente. É possível pré-processar a tabela de origem para eliminar a possibilidade de várias correspondências. Veja o exemplo de captura de dados de alteração. Este exemplo pré-processa o conjunto de dados de alteração (o conjunto de dados de origem) para reter apenas a alteração mais recente de cada chave antes de aplicar essa alteração na tabela Delta de destino. No Databricks Runtime 15.4 LTS e versões anteriores, MERGE considera apenas as condições na ON cláusula antes de avaliar várias correspondências.

Exemplos

Você pode usar MERGE INTO para operações complexas, como eliminação de duplicação de dados, executar upsert para alterar dados, aplicar operações do SCD tipo 2 etc. Confira Upsert em uma tabela do Delta Lake usando merge para obter alguns exemplos.

WHEN MATCHED

-- Delete all target rows that have a match in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED THEN DELETE

-- Conditionally update target rows that have a match in the source table using the source value.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.updated_at < source.updated_at THEN UPDATE SET *

-- Multiple MATCHED clauses conditionally deleting matched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.marked_for_deletion THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.updated_at = source.updated_at, target.value = DEFAULT

WHEN NOT MATCHED [BY TARGET]

-- Insert all rows from the source that are not already in the target table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED THEN INSERT *

-- Conditionally insert new rows in the target table using unmatched rows from the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY TARGET AND source.created_at > now() - INTERVAL “1” DAY THEN INSERT (created_at, value) VALUES (source.created_at, DEFAULT)

WHEN NOT MATCHED BY SOURCE

-- Delete all target rows that have no matches in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE THEN DELETE

-- Multiple NOT MATCHED BY SOURCE clauses conditionally deleting unmatched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE AND target.marked_for_deletion THEN DELETE
  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET target.value = DEFAULT

WITH SCHEMA EVOLUTION

-- Multiple MATCHED and NOT MATCHED clauses with schema evolution enabled.
> MERGE WITH SCHEMA EVOLUTION INTO target USING source
  ON source.key = target.key
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *
  WHEN NOT MATCHED BY SOURCE THEN DELETE

Artigos relacionados

• DELETE
• INSERT INTO
• UPDATE