MERGE

Modo de foco

MERGE - Amazon Redshift

Sintaxe Parâmetros Observações de uso Exemplos Consulte também

Mescla condicionalmente as linhas de uma tabela de origem em uma tabela de destino. Tradicionalmente, isso só pode ser feito usando várias instruções insert, update ou delete separadamente. Para obter mais informações sobre as operações que MERGE permite combinar, consulte UPDATE, DELETE e INSERT.

Sintaxe


MERGE INTO target_table 
USING source_table [ [ AS ] alias ] 
ON match_condition 
[ WHEN MATCHED THEN { UPDATE SET col_name = { expr } [,...] | DELETE }
WHEN NOT MATCHED THEN INSERT [ ( col_name [,...] ) ] VALUES ( { expr } [, ...] ) |
REMOVE DUPLICATES ]

Parâmetros

target_table

A tabela temporária ou permanente de destino da instrução MERGE.

source_table

A tabela temporária ou permanente que fornece as linhas a serem mescladas em target_table. source_table também pode ser uma tabela do Spectrum.

alias

O nome alternativo temporário para source_table.

Esse parâmetro é opcional. O alias precedente com AS também é opcional.

match_condition

Especifica predicados iguais entre a coluna da tabela de origem e a coluna da tabela de destino que são usados para determinar se as linhas em source_table podem ser combinadas com as linhas em target_table. Se a condição for atendida, MERGE executará matched_clause para essa linha. Caso contrário, MERGE executará not_matched_clause para essa linha.

WHEN MATCHED

Especifica a ação a ser executada quando a condição de correspondência entre uma linha de origem e uma linha de destino é avaliada como True. Você pode especificar uma ação UPDATE ou uma ação DELETE.

UPDATE

Atualiza a linha correspondente em target_table. Somente os valores no col_name que você especificar serão atualizados.

DELETE

Exclui a linha correspondente em target_table.

WHEN NOT MATCHED

Especifica a ação a ser executada quando a condição de correspondência é avaliada como False ou Unknown. Você só pode especificar a ação de inserção INSERT para essa cláusula.

INSERT

Insere linhas de source_table em target_table que não correspondem a nenhuma linha em target_table, de acordo com match_condition. O col_name de destino pode ser listado em qualquer ordem. Se você não fornecer nenhum valor de col_name, a ordem padrão será todas as colunas da tabela na ordem declarada.

col_name

Um ou mais nomes de coluna que você deseja modificar. Não inclui o nome da tabela ao especificar a coluna de destino.

expr

A expressão que define o novo valor para col_name.

REMOVE DUPLICATES

Especifica que o comando MERGE é executado no modo simplificado. O modo simplificado tem os seguintes requisitos:

target_table e source_table devem ter o mesmo número de colunas e tipos de coluna compatíveis.
A cláusula WHEN e as cláusulas UPDATE e INSERT devem ser omitidas do comando MERGE.
A cláusula REMOVE DUPLICATES deve ser usada no comando MERGE.

No modo simplificado, o MERGE faz o seguinte:

As linhas em target_table que têm uma correspondência em source_table são atualizadas para corresponder aos valores em source_table.
As linhas em source_table que não têm uma correspondência em target_table são inseridas em target_table.
Quando várias linhas em target_table correspondem à mesma linha em source_table, as linhas duplicadas são removidas. O Amazon Redshift mantém uma única linha e a atualiza. As linhas duplicadas que não correspondem a uma linha em source_table permanecem inalteradas.

REMOVE DUPLICATES oferece melhor performance do que WHEN MATCHED e WHEN NOT MATCHED. Recomendamos usar REMOVE DUPLICATES se target_table e source_table forem compatíveis e você não precisar preservar linhas duplicadas em target_table.

Observações de uso

Para executar instruções MERGE, você deve ser o proprietário das tabelas source_table e target_table ou ter a permissão SELECT para essas tabelas. Além disso, você deve ter as permissões UPDATE, DELETE e INSERT para target_table, dependendo das operações incluídas em sua instrução MERGE.
target_table não pode ser uma tabela do sistema, tabela de catálogo ou tabela externa.
source_table e target_table não podem ser a mesma tabela.
Não é possível usar a cláusula WITH em uma instrução MERGE.

As linhas em target_table não podem estabelecer correspondência com várias linhas em source_table.

Considere o seguinte exemplo:


CREATE TABLE target (id INT, name CHAR(10));
CREATE TABLE source (id INT, name CHAR(10));

INSERT INTO target VALUES (1, 'Bob'), (2, 'John');
INSERT INTO source VALUES (1, 'Tony'), (1, 'Alice'), (3, 'Bill');

MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);
ERROR: Found multiple matches to update the same tuple.

MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);
ERROR: Found multiple matches to update the same tuple.

Nas duas instruções MERGE, a operação falha porque há várias linhas na tabela source com um valor de ID de 1.

match_condition e expr não podem fazer referência parcial a colunas do tipo SUPER. Por exemplo, se seu objeto do tipo SUPER for uma matriz ou uma estrutura, você não poderá usar elementos individuais dessa coluna para match_condition ou expr, mas poderá usar a coluna inteira.

Considere o seguinte exemplo:


CREATE TABLE IF NOT EXISTS target (key INT, value SUPER);
CREATE TABLE IF NOT EXISTS source (key INT, value SUPER);

INSERT INTO target VALUES (1, JSON_PARSE('{"key": 88}'));
INSERT INTO source VALUES (1, ARRAY(1, 'John')), (2, ARRAY(2, 'Bill'));

MERGE INTO target USING source ON target.key = source.key
WHEN matched THEN UPDATE SET value = source.value[0]
WHEN NOT matched THEN INSERT VALUES (source.key, source.value[0]);
ERROR: Partial reference of SUPER column is not supported in MERGE statement.

Para obter mais informações sobre o tipo SUPER, consulte Tipo SUPER.

Se source_table for grande, definir as colunas de junção de target_table e source_table como chaves de distribuição poderá melhorar a performance.
Para usar a cláusula REMOVE DUPLICATES, você precisa das permissões SELECT, INSERT e DELETE para target_table.

source_table pode ser uma visualização ou subconsulta. Veja a seguir um exemplo de uma instrução MERGE em que source_table é uma subconsulta que remove linhas duplicadas.


MERGE INTO target
USING (SELECT id, name FROM source GROUP BY 1, 2) as my_source
ON target.id = my_source.id
WHEN MATCHED THEN UPDATE SET id = my_source.id, name = my_source.name
WHEN NOT MATCHED THEN INSERT VALUES (my_source.id, my_source.name);

O destino não pode ser uma fonte de dados de nenhuma subconsulta da mesma instrução MERGE. Por exemplo, o comando SQL a seguir retorna um erro como ERRO: a visualização/subconsulta de origem na instrução MERGE não pode fazer referência à tabela de destino. porque a subconsulta faz referência a target em vez de source.
```
MERGE INTO target
USING (SELECT id, name FROM target GROUP BY 1, 2) as my_source
ON target.id = my_source.id
WHEN MATCHED THEN UPDATE SET id = my_source.id, name = my_source.name
WHEN NOT MATCHED THEN INSERT VALUES (my_source.id, my_source.name);
```

Exemplos

O exemplo a seguir cria duas tabelas e executa uma operação MERGE nelas, atualizando as linhas correspondentes na tabela de destino e inserindo linhas que não correspondem. Depois, ele insere outro valor na tabela de origem e executa outra operação MERGE, desta vez excluindo as linhas correspondentes e inserindo a nova linha da tabela de origem.

Primeiro, crie e preencha as tabelas de origem e de destino.


CREATE TABLE target (id INT, name CHAR(10));
CREATE TABLE source (id INT, name CHAR(10));

INSERT INTO target VALUES (101, 'Bob'), (102, 'John'), (103, 'Susan');
INSERT INTO source VALUES (102, 'Tony'), (103, 'Alice'), (104, 'Bill');

SELECT * FROM target;
 id  |    name
-----+------------
 101 | Bob
 102 | John
 103 | Susan
(3 rows)

SELECT * FROM source;
 id  |    name
-----+------------
 102 | Tony
 103 | Alice
 104 | Bill
(3 rows)

Depois, mescle a tabela de origem com a tabela de destino, atualizando a tabela de destino com linhas correspondentes e insira linhas da tabela de origem que não tenham correspondência.


MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);

SELECT * FROM target;
 id  |    name
-----+------------
 101 | Bob
 102 | Tony
 103 | Alice
 104 | Bill
(4 rows)

Observe que as linhas com valores de id 102 e 103 são atualizadas para corresponder aos valores dos nomes da tabela de destino. Além disso, uma nova linha com um valor de id 104 e o valor de nome Bill é inserida na tabela de destino.

Depois, insira uma nova linha na tabela de origem.


INSERT INTO source VALUES (105, 'David');

SELECT * FROM source;
 id  |    name
-----+------------
 102 | Tony
 103 | Alice
 104 | Bill
 105 | David
(4 rows)

Por fim, execute uma operação de mesclagem excluindo linhas correspondentes na tabela de destino e inserindo linhas que não correspondem.


MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);

SELECT * FROM target;
 id  |    name
-----+------------
 101 | Bob
 105 | David
(2 rows)

As linhas com valores de id 102, 103 e 104 são excluídas da tabela de destino, e uma nova linha com um valor de id 105 e valor de nome David é inserida na tabela de destino.

O exemplo a seguir mostra a sintaxe simplificada de um comando MERGE que usa a cláusula REMOVE DUPLICATES.


CREATE TABLE target (id INT, name CHAR(10));
CREATE TABLE source (id INT, name CHAR(10));

INSERT INTO target VALUES (30, 'Tony'), (11, 'Alice'), (23, 'Bill');
INSERT INTO source VALUES (23, 'David'), (22, 'Clarence');

MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES;

SELECT * FROM target;
id | name
---+------------
30 | Tony
11 | Alice
23 | David
22 | Clarence
(4 rows)

O exemplo a seguir mostra a sintaxe simplificada de um comando MERGE que usa a cláusula REMOVE DUPLICATES, que remove linhas duplicadas de target_table quando elas têm linhas correspondentes em source_table.


CREATE TABLE target (id INT, name CHAR(10));
CREATE TABLE source (id INT, name CHAR(10));

INSERT INTO target VALUES (30, 'Tony'), (30, 'Daisy'), (11, 'Alice'), (23, 'Bill'), (23, 'Nikki');
INSERT INTO source VALUES (23, 'David'), (22, 'Clarence');

MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES;

SELECT * FROM target;
id | name
---+------------
30 | Tony
30 | Daisy
11 | Alice
23 | David
22 | Clarence
(5 rows)

Depois que MERGE é executado, há apenas uma linha com um valor de ID de 23 em target_table. Como não havia nenhuma linha em source_table com o valor de ID 30, as duas linhas duplicadas com valores de ID de 30 permanecem em target_table.