MERGE¶

Insere, atualiza e exclui os valores em uma tabela baseada em valores em uma segunda tabela ou em uma subconsulta. Isto pode ser útil se a segunda tabela for um log de alterações que contenha novas linhas (a serem inseridas), linhas modificadas (a serem atualizadas) e/ou linhas marcadas (a serem excluídas) na tabela de destino.

O comando oferece suporte à semântica para o tratamento dos seguintes casos:

Valores correspondentes (para atualizações e exclusões).
Valores não correspondentes (para inserções).

Consulte também:: DELETE , UPDATE

Sintaxe¶

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

Copy

Onde:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]

Copy

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Copy

Parâmetros¶

target_table: Especifica a tabela a ser fundida.
source: Especifica a tabela ou subconsulta a ser unida à tabela de destino.
join_expr: Especifica a expressão na qual se deve unir a tabela de destino e a origem.

`matchedClause` (para atualizações ou exclusões)¶

WHEN MATCHED ... THEN UPDATE <col_name> = <expr> | DELETE

Especifica a ação a ser executada quando os valores correspondem.

AND case_predicate

Opcionalmente, especifica uma expressão que, quando verdadeira, faz com que o caso correspondente seja executado.

Padrão: sem valor (caso com correspondência é sempre executado)

SET col_name = expr [ … ]

Especifica a coluna dentro da tabela de destino a ser atualizada ou inserida e a expressão correspondente para o novo valor da coluna (pode se referir tanto à relação de destino como à relação de origem).

Em uma única subcláusula SET, você pode especificar várias colunas para atualização/exclusão.

`notMatchedClause` (para inserções)¶

WHEN NOT MATCHED ... THEN INSERT

Especifica a ação a ser executada quando os valores não são correspondentes.

AND case_predicate

Opcionalmente, especifica uma expressão que, quando verdadeira, faz com que o caso não correspondente seja executado.

Padrão: sem valor (caso sem correspondência é sempre executado)

( col_name [ , ... ] )

Opcionalmente, especifica uma ou mais colunas dentro da tabela de destino a ser atualizada ou inserida.

Padrão: sem valor (todas as colunas dentro da tabela de destino são atualizadas ou inseridas)

VALUES ( expr [ , ... ] )

Especifica as expressões correspondentes para os valores das colunas inseridas (devem se referir às relações de origem).

Notas de uso¶

Uma única instrução MERGE pode incluir múltiplas cláusulas de correspondência e não correspondência (isto é, WHEN MATCHED ... e WHEN NOT MATCHED ...).
Qualquer cláusula de correspondência ou não correspondência que omita a subcláusula AND (comportamento padrão) deve ser a última cláusula do tipo na instrução (por exemplo, uma cláusula WHEN MATCHED ... não pode ser seguida por uma cláusula WHEN MATCHED AND ...). Isso resulta em um caso inalcançável, que retorna um erro.

Comportamento de junção duplicada¶

Resultados não determinísticos para UPDATE e DELETE¶

Quando uma junção une uma linha na tabela de destino com várias linhas na tabela de origem, as seguintes condições de junção produzem resultados não determinísticos (ou seja, o sistema não consegue determinar o valor de origem a ser usado para atualizar ou excluir a linha de destino):

Uma linha de destino é selecionada para ser atualizada com múltiplos valores (por exemplo, WHEN MATCHED ... THEN UPDATE).
Uma linha de destino é selecionada para ser atualizada e excluída (por exemplo, WHEN MATCHED ... THEN UPDATE , WHEN MATCHED ... THEN DELETE).

Nesta situação, o resultado da fusão depende do valor especificado para o parâmetro de sessão ERROR_ON_NONDETERMINISTIC_MERGE:

Se TRUE (valor padrão), a fusão retorna um erro.
Se FALSE, uma linha entre as duplicatas é selecionada para realizar a atualização ou exclusão; a linha selecionada não é definida.

Resultados determinísticos para UPDATE e DELETE¶

As fusões determinísticas sempre se completam sem erros. Uma fusão é determinística se atender às seguintes condições para cada linha de destino:

Uma ou mais linhas de origem satisfazem as cláusulas WHEN MATCHED ... THEN DELETE, e nenhuma outra linha de origem satisfaz qualquer cláusula WHEN MATCHED.

OR
Exatamente uma linha de origem satisfaz uma cláusula WHEN MATCHED ... THEN UPDATE, e nenhuma outra linha de origem satisfaz qualquer cláusula WHEN MATCHED.

Isto torna MERGE semanticamente equivalente aos comandos UPDATE e DELETE.

Nota

Para evitar erros quando múltiplas linhas na fonte de dados (isto é, a tabela ou subconsulta de origem) correspondem à tabela de destino com base na condição ON, use GROUP BY na cláusula de origem para garantir que cada linha de destino se una a uma linha (no máximo) na origem.

No exemplo a seguir, suponha que src inclua várias linhas com o mesmo valor k. É ambíguo quais valores (v) serão usados para atualizar linhas na linha de destino com o mesmo valor de k. Ao utilizar MAX() e GROUP BY, a consulta esclarece exatamente qual valor de v de src é utilizado:

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

Resultados determinísticos para INSERT¶

As fusões determinísticas sempre se completam sem erros.

Se o MERGE contiver uma cláusula WHEN NOT MATCHED ... THEN INSERT, e se não houver linhas correspondentes no destino, e se a fonte contiver valores duplicados, então o alvo receberá uma cópia da linha para a cópia de each na fonte. (Um exemplo está incluído abaixo).

Exemplos¶

Realizar uma simples fusão:

Criar e carregar as tabelas:

CREATE TABLE target_table (ID INTEGER, description VARCHAR);

CREATE TABLE source_table (ID INTEGER, description VARCHAR);

Copy

INSERT INTO target_table (ID, description) VALUES
    (10, 'To be updated (this is the old value)')
    ;

INSERT INTO source_table (ID, description) VALUES
    (10, 'To be updated (this is the new value)')
    ;

Copy

Executar a instrução MERGE:

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description;
+------------------------+
| number of rows updated |
|------------------------|
|                      1 |
+------------------------+

Copy

Exibir o(s) novo(s) valor(es) na tabela de destino (a tabela de origem permanece inalterada):

SELECT * FROM target_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+
SELECT * FROM source_table;
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the new value) |
+----+---------------------------------------+

Copy

Realizar uma fusão básica com uma mistura de operações (excluir, atualizar, inserir):

MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key
    WHEN MATCHED AND t2.marked = 1 THEN DELETE
    WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus
    WHEN MATCHED THEN UPDATE SET val = t2.newVal
    WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);

Copy

Realizar uma fusão em que a origem tem valores duplicados e o destino não tem valores correspondentes. Note que todas as cópias do registro de origem são inseridas no destino:

Truncar as duas tabelas e carregar novas linhas na tabela de origem. Observe que as linhas incluem duplicatas.

TRUNCATE TABLE source_table;

TRUNCATE TABLE target_table;

INSERT INTO source_table (ID, description) VALUES
    (50, 'This is a duplicate in the source and has no match in target'),
    (50, 'This is a duplicate in the source and has no match in target')
    ;

Copy

Executar a instrução MERGE:

MERGE INTO target_table USING source_table 
    ON target_table.id = source_table.id
    WHEN MATCHED THEN 
        UPDATE SET target_table.description = source_table.description
    WHEN NOT MATCHED THEN 
        INSERT (ID, description) VALUES (source_table.id, source_table.description);
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       2 |                      0 |
+-------------------------+------------------------+

Copy

Mostrar o novo valor na tabela de destino:

SELECT ID FROM target_table;
+----+
| ID |
|----|
| 50 |
| 50 |
+----+

Copy

Unir registros utilizando junções que produzem resultados não determinísticos e determinísticos:

-- Setup for example.
CREATE TABLE target_orig (k NUMBER, v NUMBER);
INSERT INTO target_orig VALUES (0, 10);

CREATE TABLE src (k NUMBER, v NUMBER);
INSERT INTO src VALUES (0, 11), (0, 12), (0, 13);

-- Multiple updates conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise updates target.v with a value (e.g. 11, 12, or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Updates and deletes conflict with each other.
-- If ERROR_ON_NONDETERMINISTIC_MERGE=true, returns an error;
-- otherwise either deletes the row or updates target.v with a value (e.g. 12 or 13) from one of the duplicate rows (row not defined).

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.v = src.v;

-- Multiple deletes do not conflict with each other;
-- joined values that do not match any clause do not prevent the delete (src.v = 13).
-- Merge succeeds and the target row is deleted.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v <= 12 THEN DELETE;

-- Joined values that do not match any clause do not prevent an update (src.v = 12, 13).
-- Merge succeeds and the target row is set to target.v = 11.

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target
  USING src ON target.k = src.k
  WHEN MATCHED AND src.v = 11 THEN UPDATE SET target.v = src.v;

-- Use GROUP BY in the source clause to ensure that each target row joins against one row
-- in the source:

CREATE OR REPLACE TABLE target CLONE target_orig;

MERGE INTO target USING (select k, max(v) as v from src group by k) AS b ON target.k = b.k
  WHEN MATCHED THEN UPDATE SET target.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);

Copy

No exemplo a seguir, a tabela members armazena os nomes, endereços e taxas atuais (members.fee) pagas a uma academia local. A tabela signup armazena a data de matrícula de cada membro (signup.date). A instrução MERGE aplica uma taxa padrão de $40 aos membros que ingressaram na academia há mais de 30 dias, depois que o período de experiência gratuita expirou:

MERGE INTO members m
  USING (
  SELECT id, date
  FROM signup
  WHERE DATEDIFF(day, CURRENT_DATE(), signup.date::DATE) < -30) s ON m.id = s.id
  WHEN MATCHED THEN UPDATE SET m.fee = 40;

Copy

MERGE¶

Sintaxe¶

Parâmetros¶

matchedClause (para atualizações ou exclusões)¶

notMatchedClause (para inserções)¶

Notas de uso¶

Comportamento de junção duplicada¶

Resultados não determinísticos para UPDATE e DELETE¶

Resultados determinísticos para UPDATE e DELETE¶

Resultados determinísticos para INSERT¶

Exemplos¶

`matchedClause` (para atualizações ou exclusões)¶

`notMatchedClause` (para inserções)¶