MERGE

Insere, atualiza e exclui os valores em uma tabela que são baseados em valores em uma segunda tabela ou em uma subconsulta. A mesclagem 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) 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 { ALL BY NAME | SET <col_name> = <expr> [ , <col_name> = <expr> ... ] } | DELETE } [ ... ]
Copy
notMatchedClause ::=
   WHEN NOT MATCHED
     [ AND <case_predicate> ]
     THEN INSERT { ALL BY NAME | [ ( <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 ... 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)

WHEN MATCHED ... THEN { UPDATE { ALL BY NAME | SET ... } | DELETE }

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

ALL BY NAME

Atualiza todas as colunas da tabela de destino com valores da origem. Cada coluna na tabela de destino é atualizada com os valores da coluna com o mesmo nome da origem.

A tabela de destino e a origem devem ter o mesmo número de colunas e os mesmos nomes para todas as colunas. No entanto, a ordem das colunas pode ser diferente entre a tabela de destino e a origem.

SET col_name = expr [ , col_name = expr ... ]

Atualiza a coluna especificada na tabela de destino usando 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.

DELETE

Exclui as linhas na tabela de destino quando elas correspondem à origem.

notMatchedClause (para inserções)

WHEN NOT MATCHED ... 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)

WHEN NOT MATCHED ... THEN INSERT . { ALL BY NAME | [ ( col_name [ , ... ] ) ] VALUES ( expr [ , ... ] ) }

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

ALL BY NAME

Insere todas as colunas na tabela de destino com valores da origem. Cada coluna na tabela de destino é inserida com os valores da coluna com o mesmo nome da origem.

A tabela de destino e a origem devem ter o mesmo número de colunas e os mesmos nomes para todas as colunas. No entanto, a ordem das colunas pode ser diferente entre a tabela de destino e a origem.

( col_name [ , ... ] )

Especifica opcionalmente uma ou mais colunas na tabela de destino a serem inseridas com valores da origem.

Padrão: sem valor (todas as colunas da tabela de destino são 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

Quando múltiplas linhas na tabela de origem correspondem a uma única linha na tabela de destino, os resultados podem ser determinísticos ou não determinísticos. Esta seção descreve MERGE Comportamento do para esses casos de uso.

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 pelo menos uma das 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.

  • 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. Usando a função MAX e GROUP BY, a consulta esclarece exatamente qual valor de v de src é usado:

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 a instruçã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 cada cópia na fonte. Para obter um exemplo, consulte Realizar uma fusão com duplicatas de origem.

Exemplos

Os exemplos a seguir usam o comando MERGE:

Executar uma mesclagem básica que atualiza os valores

O exemplo a seguir realiza uma mesclagem básica que atualiza os valores na tabela de destino usando os valores da tabela de origem. Crie e carregue duas tabelas:

CREATE OR REPLACE TABLE merge_example_target (id INTEGER, description VARCHAR);

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

CREATE OR REPLACE TABLE merge_example_source (id INTEGER, description VARCHAR);

INSERT INTO merge_example_source (id, description) VALUES
  (10, 'To be updated (this is the new value)');
Copy

Exibir os valores nas tabelas:

SELECT * FROM merge_example_target;
Copy
+----+---------------------------------------+
| ID | DESCRIPTION                           |
|----+---------------------------------------|
| 10 | To be updated (this is the old value) |
+----+---------------------------------------+

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

Execute a instrução MERGE:

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

Mostrar os novos valores na tabela de destino (a tabela de origem permanece inalterada):

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

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

Realizar uma fusão básica com múltiplas operações

Executar uma fusão básica com uma mistura de operações (INSERT, UPDATEe DELETE).

Crie e carregue duas tabelas:

CREATE OR REPLACE TABLE merge_example_mult_target (
  id INTEGER,
  val INTEGER,
  status VARCHAR);

INSERT INTO merge_example_mult_target (id, val, status) VALUES
  (1, 10, 'Production'),
  (2, 20, 'Alpha'),
  (3, 30, 'Production');

CREATE OR REPLACE TABLE merge_example_mult_source (
  id INTEGER,
  marked VARCHAR,
  isnewstatus INTEGER,
  newval INTEGER,
  newstatus VARCHAR);

INSERT INTO merge_example_mult_source (id, marked, isnewstatus, newval, newstatus) VALUES
  (1, 'Y', 0, 10, 'Production'),
  (2, 'N', 1, 50, 'Beta'),
  (3, 'N', 0, 60, 'Deprecated'),
  (4, 'N', 0, 40, 'Production');
Copy

Exibir os valores nas tabelas:

SELECT * FROM merge_example_mult_target;
Copy
+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  1 |  10 | Production |
|  2 |  20 | Alpha      |
|  3 |  30 | Production |
+----+-----+------------+

SELECT * FROM merge_example_mult_source;
Copy
+----+--------+-------------+--------+------------+
| ID | MARKED | ISNEWSTATUS | NEWVAL | NEWSTATUS  |
|----+--------+-------------+--------+------------|
|  1 | Y      |           0 |     10 | Production |
|  2 | N      |           1 |     50 | Beta       |
|  3 | N      |           0 |     60 | Deprecated |
|  4 | N      |           0 |     40 | Production |
+----+--------+-------------+--------+------------+

O exemplo de fusão a seguir executa as seguintes ações no merge_example_mult_target Tabela:

  • Exclui a linha com id definir como 1 porque o marked coluna para a linha com o mesmo id é Y em merge_example_mult_source.

  • Atualizações de val e status Valores na linha com id definir como 2 com valores na linha com o mesmo id em merge_example_mult_source, porque isnewstatus está definido como 1 para a mesma linha em merge_example_mult_source.

  • Atualizações de val Valor na linha com id definir como 3 com o valor na linha com o mesmo id em merge_example_mult_source. O MERGE A instrução não atualiza o status valor em merge_example_mult_target porque isnewstatus está definido como 0 para esta linha em merge_example_mult_source.

  • Insere a linha com id definir como 4 porque a linha existe em merge_example_mult_source e não há nenhuma linha correspondente em merge_example_mult_target.

MERGE INTO merge_example_mult_target
  USING merge_example_mult_source
  ON merge_example_mult_target.id = merge_example_mult_source.id
  WHEN MATCHED AND merge_example_mult_source.marked = 'Y'
    THEN DELETE
  WHEN MATCHED AND merge_example_mult_source.isnewstatus = 1
    THEN UPDATE SET val = merge_example_mult_source.newval, status = merge_example_mult_source.newstatus
  WHEN MATCHED
    THEN UPDATE SET val = merge_example_mult_source.newval
  WHEN NOT MATCHED
    THEN INSERT (id, val, status) VALUES (
      merge_example_mult_source.id,
      merge_example_mult_source.newval,
      merge_example_mult_source.newstatus);
Copy
+-------------------------+------------------------+------------------------+
| number of rows inserted | number of rows updated | number of rows deleted |
|-------------------------+------------------------+------------------------|
|                       1 |                      2 |                      1 |
+-------------------------+------------------------+------------------------+

Para ver os resultados da fusão, exiba os valores na merge_example_mult_target Tabela:

SELECT * FROM merge_example_mult_target ORDER BY id;
Copy
+----+-----+------------+
| ID | VAL | STATUS     |
|----+-----+------------|
|  2 |  50 | Beta       |
|  3 |  60 | Production |
|  4 |  40 | Production |
+----+-----+------------+

Realizar uma fusão usando ALL BY NAME

O exemplo a seguir realiza uma fusão que insere e atualiza valores na tabela de destino usando valores da tabela de origem. O exemplo usa o WHEN MATCHED ... THEN ALL BY NAME e WHEN NOT MATCHED ... THEN ALL BY NAME Subcláusulas para especificar que a fusão se aplica a todas as colunas.

Crie duas tabelas com o mesmo número de colunas e os mesmos nomes para as colunas, mas com uma ordem diferente para duas das colunas:

CREATE OR REPLACE TABLE merge_example_target_all (
  id INTEGER,
  x INTEGER,
  y VARCHAR);

CREATE OR REPLACE TABLE merge_example_source_all (
  id INTEGER,
  y VARCHAR,
  x INTEGER);
Copy

Carregue as tabelas:

INSERT INTO merge_example_target_all (id, x, y) VALUES
  (1, 10, 'Skiing'),
  (2, 20, 'Snowboarding');

INSERT INTO merge_example_source_all (id, y, x) VALUES
  (1, 'Skiing', 10),
  (2, 'Snowboarding', 25),
  (3, 'Skating', 30);
Copy

Exibir os valores nas tabelas:

SELECT * FROM merge_example_target_all;
Copy
+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 20 | Snowboarding |
+----+----+--------------+

SELECT * FROM merge_example_source_all;
Copy
+----+--------------+----+
| ID | Y            |  X |
|----+--------------+----|
|  1 | Skiing       | 10 |
|  2 | Snowboarding | 25 |
|  3 | Skating      | 30 |
+----+--------------+----+

Execute a instrução MERGE:

MERGE INTO merge_example_target_all
  USING merge_example_source_all
  ON merge_example_target_all.id = merge_example_source_all.id
  WHEN MATCHED THEN
    UPDATE ALL BY NAME
  WHEN NOT MATCHED THEN
    INSERT ALL BY NAME;
Copy
+-------------------------+------------------------+
| number of rows inserted | number of rows updated |
|-------------------------+------------------------|
|                       1 |                      2 |
+-------------------------+------------------------+

Mostrar os novos valores na tabela de destino:

SELECT *
  FROM merge_example_target_all
  ORDER BY id;
Copy
+----+----+--------------+
| ID |  X | Y            |
|----+----+--------------|
|  1 | 10 | Skiing       |
|  2 | 25 | Snowboarding |
|  3 | 30 | Skating      |
+----+----+--------------+

Realizar uma fusão com duplicatas de origem

Realizar uma fusão em que a origem tem valores duplicados e o destino não tem valores correspondentes. Todas as cópias do registro de origem são inseridas no destino. Para obter mais informações, consulte Resultados determinísticos para INSERT.

Truncar as duas tabelas e carregar novas linhas na tabela de origem que incluam duplicatas:

TRUNCATE table merge_example_target;

TRUNCATE table merge_example_source;

INSERT INTO merge_example_source (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

O merge_example_target não tem valores. Mostrar os valores na tabela merge_example_source:

SELECT * FROM merge_example_source;
Copy
+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Execute a instrução MERGE:

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

Mostrar os novos valores na tabela de destino:

SELECT * FROM merge_example_target;
Copy
+----+--------------------------------------------------------------+
| ID | DESCRIPTION                                                  |
|----+--------------------------------------------------------------|
| 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 |
+----+--------------------------------------------------------------+

Realizar uma fusão com resultados determinísticos e não determinísticos

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

Crie e carregue duas tabelas:

CREATE OR REPLACE TABLE merge_example_target_orig (k NUMBER, v NUMBER);

INSERT INTO merge_example_target_orig VALUES (0, 10);

CREATE OR REPLACE TABLE merge_example_src (k NUMBER, v NUMBER);

INSERT INTO merge_example_src VALUES (0, 11), (0, 12), (0, 13);
Copy

Quando você realiza a fusão no exemplo a seguir, várias atualizações entram em conflito entre si. Se o ERROR_ON_NONDETERMINISTIC_MERGE o parâmetro de sessão é definido como true, o MERGE A instrução retorna um erro. Caso contrário, o MERGE Atualizações de instrução merge_example_target_clone.v com um valor (por exemplo, 11, 12``ou ``13) de uma das linhas duplicadas (linha não definida):

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO  merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Atualizações e exclusões entram em conflito entre si. Se o ERROR_ON_NONDETERMINISTIC_MERGE o parâmetro de sessão é definido como true, o MERGE A instrução retorna um erro. Caso contrário, o MERGE A instrução exclui a linha ou atualiza merge_example_target_clone.v com um valor (por exemplo, 12 ou 13) de uma das linhas duplicadas (linha não definida):

CREATE OR REPLACE TABLE merge_example_target_clone
  CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v = 11 THEN DELETE
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Várias exclusões não entram em conflito umas com as outras. Os valores unidos que não correspondem a nenhuma cláusula não impedem a exclusão (merge_example_src.v = 13). O MERGE A instrução é bem-sucedida e a linha de destino é excluída:

CREATE OR REPLACE TABLE target CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v <= 12 THEN DELETE;
Copy

Os valores unidos que não correspondem a nenhuma cláusula não impedem uma atualização (merge_example_src.v = 12, 13). A instrução MERGE é bem-sucedida e a linha de destino é definida como target.v = 11:

CREATE OR REPLACE TABLE merge_example_target_clone CLONE target_orig;

MERGE INTO merge_example_target_clone
  USING merge_example_src
  ON merge_example_target_clone.k = merge_example_src.k
  WHEN MATCHED AND merge_example_src.v = 11
    THEN UPDATE SET merge_example_target_clone.v = merge_example_src.v;
Copy

Uso GROUP BY na cláusula de origem para garantir que cada linha de destino se una a uma linha na origem:

CREATE OR REPLACE TABLE merge_example_target_clone CLONE merge_example_target_orig;

MERGE INTO merge_example_target_clone
  USING (SELECT k, MAX(v) AS v FROM merge_example_src GROUP BY k) AS b
  ON merge_example_target_clone.k = b.k
  WHEN MATCHED THEN UPDATE SET merge_example_target_clone.v = b.v
  WHEN NOT MATCHED THEN INSERT (k, v) VALUES (b.k, b.v);
Copy

Execução de uma fusão baseada em DATE Valores

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
Linguagem: Português