MERGE

Inserts, updates, and deletes values in a table that are based on values in a second table or a subquery. Merging can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), or marked rows (to be deleted) in the target table.

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

  • Valores correspondentes (para atualizações e exclusões).

  • Values that don’t match (for inserts).

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 ... ]

Updates the specified column in the target table by using the corresponding expression for the new column value (can refer to both the target and source relations).

In a single SET subclause, you can specify multiple columns to update.

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 [ , ... ] ) }

Specifies the action to perform when the values don’t match.

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 [ , ... ] )

Optionally specifies one or more columns in the target table to be inserted with values from the source.

Default: No value (all columns in the target table are inserted)

VALUES ( expr [ , ... ] )

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

Notas de uso

  • A single MERGE statement can include multiple matching and not-matching clauses (that is, WHEN MATCHED ... and WHEN NOT MATCHED ...).

  • Any matching or not-matching clause that omits the AND subclause (default behavior) must be the last of its clause type in the statement (for example, a WHEN MATCHED ... clause can’t be followed by a WHEN MATCHED AND ... clause). Doing so results in an unreachable case, which returns an error.

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

When a merge joins a row in the target table against multiple rows in the source, the following join conditions produce nondeterministic results (that is, the system is unable to determine the source value to use to update or delete the target row):

  • A target row is selected to be updated with multiple values (for example, WHEN MATCHED ... THEN UPDATE).

  • A target row is selected to be both updated and deleted (for example, 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

Deterministic merges always complete without error. A merge is deterministic if it meets at least one of the following conditions for each target row:

  • 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

To avoid errors when multiple rows in the data source (that is, the source table or subquery) match the target table based on the ON condition, use GROUP BY in the source clause to ensure that each target row joins against one row (at most) in the source.

In the following example, assume src includes multiple rows with the same k value. It’s ambiguous which values (v) will be used to update rows in the target row with the same value of k. By using the MAX function and GROUP BY, the query clarifies exactly which value of v from src is used:

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.

If the MERGE statement contains a WHEN NOT MATCHED ... THEN INSERT clause, and if there are no matching rows in the target, and if the source contains duplicate values, then the target gets one copy of the row for each copy in the source. For an example, see Realizar uma fusão com duplicatas de origem.

Exemplos

Os exemplos a seguir usam o comando MERGE:

Perform a basic merge that updates values

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

Display the values in the tables:

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) |
+----+---------------------------------------+

Run the MERGE statement:

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 |
+------------------------+

Display the new values in the target table (the source table is unchanged):

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) |
+----+---------------------------------------+

Perform a basic merge with multiple operations

Perform a basic merge with a mix of operations (INSERT, UPDATE, and DELETE).

Create and load two tables:

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

Display the values in the tables:

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

Load the tables:

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

Display the values in the tables:

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 |
+----+--------------+----+

Run the MERGE statement:

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 |
+-------------------------+------------------------+

Display the new values in the target table:

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

Perform a merge in which the source has duplicate values and the target has no matching values. All copies of the source record are inserted into the target. For more information, see Resultados determinísticos para INSERT.

Truncate both tables and load new rows into the source table that include duplicates:

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 |
+----+--------------------------------------------------------------+

Run the MERGE statement:

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 |
+-------------------------+------------------------+

Display the new values in the target table:

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 |
+----+--------------------------------------------------------------+

Perform a merge with deterministic and nondeterministic results

Merge records by using joins that produce nondeterministic and deterministic results.

Create and load two tables:

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