MERGE

MERGE
Anterior	Acima	Comandos SQL	Principal	Próxima

MERGE — inserir, atualizar ou excluir linhas de uma tabela condicionalmente.

Sinopse

[ WITH consulta_WITH [, ...] ]
MERGE INTO [ ONLY ] nome_da_tabela_alvo [ * ] [ [ AS ] alias_do_alvo ]
    USING fonte_de_dados ON condição_de_junção
    cláusula_WHEN [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS alias_da_saída [, ...] ) ]
        { * | expressão_de_saída
        [ [ AS ] nome_da_saída ]
        }
        [, ...]
    ]

onde fonte_de_dados é:

    { [ ONLY ] nome_da_tabela_fonte [ * ]
      | ( consulta_fonte )
    }
    [ [ AS ] alias_da_fonte ]

e cláusula_WHEN é:

    { WHEN MATCHED [ AND condição ] THEN {
            atualização_da_mesclagem
          | exclusão_da_mesclagem
          | DO NOTHING
      }
      | WHEN NOT MATCHED BY SOURCE [ AND condição ] THEN {
            atualização_da_mesclagem
          | exclusão_da_mesclagem
          | DO NOTHING
      }
      | WHEN NOT MATCHED [ BY TARGET ] [ AND condição ] THEN {
            inserção_da_mesclagem
          | DO NOTHING
      }
    }

e inserção_da_mesclagem é:

    INSERT [( nome_da_coluna [, ...] )]
        [ OVERRIDING { SYSTEM | USER } VALUE ]
        { VALUES ( { expressão | DEFAULT } [, ...] )
        | DEFAULT VALUES
        }

e atualização_da_mesclagem é:

    UPDATE SET {
          nome_da_coluna = { expressão | DEFAULT }
      | ( nome_da_coluna [, ...] ) = [ ROW ]
        ( { expressão | DEFAULT } [, ...] )
      | ( nome_da_coluna [, ...] ) = ( sub-SELECT )
    }
    [, ...]

e exclusão_da_mesclagem é:

    DELETE

Descrição

O comando MERGE executa ações que modificam linhas na tabela de destino identificada por nome_da_tabela_alvo, usando uma fonte_de_dados. O comando MERGE permite que um único comando SQL execute os comandos INSERT, UPDATE ou DELETE nas linhas dependendo da condição, uma tarefa que, de outra forma, exigiria várias instruções em uma linguagem procedural ^[150].

Primeiro, o comando MERGE realiza uma junção da fonte_de_dados com a tabela de destino, produzindo zero ou mais linhas candidatas a alterações. Para cada linha candidata à alteração, o status de MATCHED, NOT MATCHED BY SOURCE ou NOT MATCHED [BY TARGET] é definido apenas uma vez, após o que as cláusulas WHEN são avaliadas na ordem especificada. Para cada linha candidata à alteração, a primeira cláusula que for avaliada como verdade é executada. Não mais do que uma cláusula WHEN é executada para qualquer linha candidata à alteração.

As ações do comando MERGE têm o mesmo efeito que os comandos UPDATE, INSERT ou DELETE regulares com os mesmos nomes. A sintaxe desses comandos é diferente, principalmente pelo fato de não haver uma cláusula WHERE e não ser especificado nenhum nome de tabela. Todas as ações se referem à tabela de destino, embora possam ser feitas modificações em outras tabelas usando gatilhos.

Quando é especificada a opção DO NOTHING a linha de origem é pulada. Como as ações são avaliadas na ordem especificada, a opção DO NOTHING pode ser útil para ignorar linhas de origem sem relevância antes de um processamento mais detalhado.

A cláusula opcional RETURNING faz com que o comando MERGE calcule e retorne um ou mais valores com base em cada linha inserida, atualizada ou excluída. É possível calcular qualquer expressão que utilize as colunas da tabela de origem ou de destino, ou a função merge_action(). Por padrão, quando é executada uma ação INSERT ou UPDATE, são usados os novos valores das colunas da tabela de destino, e quando é executada uma ação DELETE, são usados os valores antigos das colunas da tabela de destino, mas também é possível especificar explicitamente os valores antigos e novos. A sintaxe da lista RETURNING é idêntica à da lista de saída do comando SELECT.

Não existe um privilégio MERGE separado. Se for especificada uma ação de UPDATE será necessário possuir o privilégio UPDATE nas colunas da tabela de destino referenciadas na cláusula SET. Se for especificada uma ação de inserção, será necessário possuir o privilégio INSERT na tabela de destino. Se for especificada uma ação de exclusão, será necessário possuir o privilégio DELETE na tabela de destino. Se for especificada uma ação DO NOTHING, será necessário possuir o privilégio SELECT em pelo menos uma coluna da tabela de destino. Também é necessário possuir o privilégio SELECT na(s) coluna(s) da fonte_de_dados e da tabela de destino referida(s) em qualquer condição (incluindo a condição_de_junção) e a expressão. Os privilégios são testados uma vez no início do comando para verificar se determinadas cláusulas WHEN serão executadas ou não.

O comando MERGE não terá suporte se a tabela de destino for uma visão materializada, uma tabela estrangeira, ou se houver alguma regra definida nela.

Parâmetros

consulta_WITH

A cláusula WITH permite especificar uma ou mais subconsultas que podem ser referenciadas por nome na consulta MERGE. Veja Consultas WITH (Expressões de tabela comuns) e SELECT para obter detalhes. Note que o comando MERGE não oferece suporte a WITH RECURSIVE.

nome_da_tabela_alvo

O nome (opcionalmente qualificado pelo esquema) da tabela ou visão de destino para a qual será feita a mesclagem. Se for indicado ONLY antes do nome de uma tabela, as linhas correspondentes serão atualizadas ou excluídas somente na tabela especificada. Se não for indicado ONLY, as linhas correspondentes também serão atualizadas ou excluídas em quaisquer tabelas que herdem da tabela especificada. Opcionalmente, pode ser especificado * após o nome da tabela para indicar explicitamente que as tabelas descendentes estão incluídas. A palavra-chave ONLY e a opção * não afetam as ações de inserção, que sempre inserem apenas na tabela especificada.

Se nome_da_tabela_alvo for uma visão, ela deverá ser atualizável automaticamente, sem gatilhos do tipo INSTEAD OF, ou deverá ter gatilhos INSTEAD OF para todos os tipos de ação (INSERT, UPDATE e DELETE) especificadas na cláusula WHEN. Não há suporte para visão com regras.

alias_do_alvo

Um nome substituto para a tabela de destino. Quando é fornecido um alias, ele oculta completamente o nome real da tabela. Por exemplo, dado MERGE INTO foo AS f, o restante do comando MERGE deverá se referir a esta tabela como f e não foo.

nome_da_tabela_fonte

O nome (opcionalmente qualificado pelo esquema) da tabela de origem, da visão ou da tabela de transição. Se for indicado ONLY antes do nome da tabela, serão incluídas apenas as linhas correspondentes da tabela especificada. Se não for indicado ONLY, as linhas correspondentes também serão incluídas de quaisquer tabelas que herdem da tabela especificada. Opcionalmente, pode ser especificado * após o nome da tabela para indicar explicitamente que as tabelas descendentes estão incluídas.

consulta_fonte

A consulta (o comando SELECT ou VALUES) que fornece as linhas a serem mescladas na tabela de destino. Veja nos comandos SELECT ou VALUES a descrição da sintaxe.

alias_da_fonte

Um nome substituto para a fonte de dados. Quando é fornecido um alias, ele oculta inteiramente o nome real da tabela ou o fato de ter sido realizada uma consulta.

condição_de_junção

A condição_de_junção é uma expressão que resulta em um valor do tipo de dados boolean (semelhante a uma cláusula WHERE) que especifica quais linhas na fonte_de_dados correspondem a linhas na tabela de destino.

Atenção

Somente as colunas da tabela de destino que se pretende fazer correspondência com as linhas da fonte_de_dados deverão aparecer na condição_de_junção. Subexpressões da condição_de_junção que fazem referência apenas às colunas da tabela de destino podem afetar a ação tomada, muitas vezes de maneiras surpreendentes.

Se for especificada tanto a cláusula WHEN NOT MATCHED BY SOURCE quanto a cláusula WHEN NOT MATCHED [BY TARGET], o comando MERGE irá realizar uma junção FULL entre a fonte_de_dados e a tabela de destino. Para que isto funcione, pelo menos uma subexpressão da condição_de_junção deve usar um operador que ofereça suporte para uma junção por hash, ou todas as subexpressões devem usar operadores que ofereçam suporte para uma junção por mesclagem.

cláusula_WHEN

É necessário existir pelo menos uma cláusula WHEN.

A cláusula WHEN pode especificar WHEN MATCHED, WHEN NOT MATCHED BY SOURCE ou WHEN NOT MATCHED [BY TARGET]. Note que o padrão SQL define apenas WHEN MATCHED e WHEN NOT MATCHED (que significa nenhuma linha de destino correspondente). WHEN NOT MATCHED BY SOURCE é uma extensão ao padrão SQL, assim como é a opção de apensar BY TARGET a WHEN NOT MATCHED, para tornar seu significado mais explícito.

▸ Há correspondência entre origem e destino:: Se a cláusula WHEN especificar WHEN MATCHED e a linha candidata à alteração representar uma linha na fonte_de_dados que corresponda a uma linha na tabela de destino, a cláusula WHEN será executada se a condição estiver ausente ou se for avaliada como true.
▸ Não há correspondência entre destino e origem:: Se a cláusula WHEN especificar WHEN NOT MATCHED BY SOURCE e a linha candidata à alteração representar uma linha na tabela de destino que não corresponda a uma linha na fonte_de_dados, a cláusula WHEN será executada se a condição estiver ausente ou se for avaliada como true.
▸ Não há correspondência entre origem e destino:: Se a cláusula WHEN especificar WHEN NOT MATCHED [BY TARGET] e a linha candidata à alteração representar uma linha na fonte_de_dados que não corresponda a uma linha na tabela de destino a cláusula WHEN será executada se a condição estiver ausente ou se for avaliada como true.

condição

Uma expressão que retorna um valor do tipo de dados boolean. Se esta expressão para uma cláusula WHEN retornar true, então a ação para esta cláusula será executada para esta linha.

Uma condição em uma cláusula WHEN MATCHED pode se referir a colunas tanto nas relações de origem quanto nas de destino. Uma condição em uma cláusula WHEN NOT MATCHED BY SOURCE só pode se referir a colunas da relação de destino já que, por definição, não existe uma linha de origem correspondente. Uma condição em uma cláusula WHEN NOT MATCHED [BY TARGET] só pode se referir a colunas da relação de origem já que, por definição, não há uma linha de destino correspondente. Somente são acessíveis os atributos do sistema da tabela de destino.

inserção_da_mesclagem

A especificação de uma ação INSERT que insere uma linha na tabela de destino. Os nomes das colunas de destino podem ser listados em qualquer ordem. Caso não seja fornecida nenhuma lista de nomes de colunas, o padrão será todas as colunas da tabela na ordem em que foram declaradas.

Cada coluna não presente na lista de colunas explícita ou implícita será preenchida com um valor padrão, seja o seu valor padrão declarado ou nulo caso não haja nenhum.

Se a tabela de destino for uma tabela particionada, cada linha será encaminhada para a partição apropriada e inserida nela. Se a tabela de destino for uma partição, ocorrerá um erro se alguma linha de entrada violar a restrição de partição.

Os nomes das colunas não podem ser especificados mais de uma vez. As ações INSERT não podem conter subconsultas.

Pode ser especificada apenas uma cláusula VALUES. A cláusula VALUES só pode se referir a colunas da relação de origem já que, por definição, não existe uma linha de destino correspondente.

atualização_da_mesclagem

A especificação de uma ação UPDATE que atualiza a linha corrente da tabela de destino. Os nomes das colunas não podem ser especificados mais de uma vez.

Não são permitidos nomes de tabelas nem cláusulas WHERE.

exclusão_da_mesclagem

A especificação de uma ação DELETE que exclui a linha corrente da tabela de destino. Não se deve incluir o nome da tabela ou quaisquer outras cláusulas, como normalmente se faria em um comando DELETE.

nome_da_coluna

O nome de uma coluna na tabela de destino. O nome da coluna pode ser qualificado com um nome de subcampo ou um índice de matriz, se necessário. (Inserir dados apenas em alguns campos de uma coluna composta deixa os outros campos nulos.) Não se deve incluir o nome da tabela na especificação de uma coluna de destino.

OVERRIDING SYSTEM VALUE

Sem esta cláusula, seria errado especificar um valor explícito (diferente de DEFAULT) para uma coluna de identidade definida como GENERATED ALWAYS. Esta cláusula se sobrepõe a esta restrição.

OVERRIDING USER VALUE

Se for especificada esta cláusula, quaisquer valores fornecidos para colunas de identidade definidas como GENERATED BY DEFAULT serão ignorados e serão aplicados os valores padrão gerados pela sequência.

DEFAULT VALUES

Todas as colunas serão preenchidas com seus valores padrão. (A cláusula OVERRIDING não é permitida nesta modalidade.)

expressão

Uma expressão a ser atribuída à coluna. Se for usada em uma cláusula WHEN MATCHED, a expressão poderá usar valores da linha original na tabela de destino e valores da linha de fonte_de_dados. Se for usada em uma cláusula WHEN NOT MATCHED BY SOURCE, a expressão só poderá usar valores da linha original na tabela de destino. Se for usada em uma cláusula WHEN NOT MATCHED [BY TARGET], a expressão só poderá usar valores da linha de fonte_de_dados.

DEFAULT

Define a coluna com seu valor padrão (que será NULL se nenhuma expressão padrão específica tiver sido atribuída à coluna).

sub-SELECT

Uma subconsulta SELECT que produz tantas colunas de saída quantas forem listadas na lista de colunas entre parênteses que a precede. A subconsulta não deve retornar mais de uma linha quando executada. Se retornar uma única linha, os valores de suas colunas serão atribuídos às colunas de destino; se não retornar nenhuma linha, serão atribuídos valores NULL às colunas de destino. Se for usada em uma cláusula WHEN MATCHED, a subconsulta poderá se referir a valores da linha original na tabela de destino e a valores da linha de fonte_de_dados. Se for usada em uma cláusula WHEN NOT MATCHED BY SOURCE, a subconsulta só poderá se referir a valores da linha original na tabela de destino.

alias_da_saída

Um nome substituto opcional para as linhas OLD ou NEW na lista de RETURNING.

Por padrão, os valores antigos da tabela de destino podem ser retornados escrevendo OLD.nome_da_coluna ou OLD.*, e novos valores podem ser retornados escrevendo NEW.nome_da_coluna ou NEW.*. Quando é fornecido um alias, estes nomes ficam ocultos e as linhas antigas ou novas devem ser referenciadas usando o alias. Por exemplo RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.

expressão_de_saída

Uma expressão a ser calculada e retornada pelo comando MERGE após cada alteração em uma linha (seja de inserção, atualização ou exclusão). A expressão pode usar quaisquer colunas das tabelas de origem ou de destino ou a função merge_action() para retornar informações adicionais sobre a ação executada.

Escrever * retorna todas as colunas da tabela de origem, seguidas por todas as colunas da tabela de destino. Frequentemente, isto leva a muita duplicidade, já que é comum as tabelas de origem e de destino terem muitas colunas em comum. Isto pode ser evitado qualificando o * com o nome ou alias da tabela de origem ou de destino.

Um nome de coluna ou * também pode ser qualificado usando OLD ou NEW, ou o alias_da_saída correspondente para OLD ou NEW, para que valores antigos ou novos da tabela de destino sejam retornados. Um nome de coluna não qualificado da tabela de destino, ou um nome de coluna ou * qualificado usando o nome ou alias da tabela de destino irá retornar os novos valores para as ações INSERT e UPDATE, e os valores antigos para as ações DELETE.

nome_da_saída

O nome a ser usado para uma coluna retornada.

Saídas

Após a conclusão bem-sucedida, o comando MERGE retorna uma saída de comando no formato

MERGE total_count

Onde total_count é o número total de linhas alteradas (sejam elas inseridas, atualizadas ou excluídas). Se total_count for 0, nenhuma linha foi alterada de alguma forma.

Se o comando MERGE contiver uma cláusula RETURNING, o resultado será semelhante ao de um comando SELECT contendo as colunas e os valores definidos na lista RETURNING, calculada sobre a(s) linha(s) inserida(s), atualizada(s) ou excluída(s) pelo comando.

Notas

As seguintes etapas ocorrem durante a execução do comando MERGE.

Execução de todos os gatilhos BEFORE STATEMENT de todas ações especificadas, independentemente de suas cláusulas WHEN terem correspondência ou não.
Realização da junção da tabela de origem com a tabela de destino. A consulta resultante será otimizada como de costume produzindo um conjunto de linhas candidatas à alteração. Para cada linha candidata à alteração,
1. Avalia se cada linha é MATCHED, NOT MATCHED BY SOURCE ou NOT MATCHED [BY TARGET].
2. Testa cada condição WHEN na ordem especificada até que uma retorne verdade.
3. Quando uma condição retorna verdade, executa as seguintes ações:
  1. Executa quaisquer gatilhos BEFORE ROW que sejam acionados para o tipo de evento da ação.
  2. Executa a ação especificada, aplicando quaisquer restrições de verificação na tabela de destino.
  3. Executa quaisquer gatilhos AFTER ROW que sejam acionados para o tipo de evento da ação.
  Se a relação de destino for uma visão com gatilhos INSTEAD OF ROW para o tipo de evento da ação, estes serão usados para realizar a ação.
Executa quaisquer gatilhos AFTER STATEMENT para as ações especificadas, independentemente de elas realmente ocorrerem ou não. Isto é semelhante ao comportamento de um comando UPDATE que não modifica nenhuma linha.

Em resumo, os gatilhos no nível de comando para um tipo de evento (digamos, INSERT) serão disparados sempre que uma ação desse tipo for especificada. Em contraste, os gatilhos no nível de linha serão disparados apenas para o tipo de evento específico que está sendo executado. Assim, o comando MERGE poderá disparar gatilhos no nível de comando tanto para UPDATE quanto para INSERT, mesmo que somente gatilhos no nível de linha para UPDATE tenham sido disparados.

Deve-se garantir que a junção produza no máximo uma linha candidata à alteração para cada linha de destino. Em outras palavras, uma linha de destino não deve ser associada a mais de uma linha da origem dos dados. Caso isto ocorra, apenas uma das linhas candidatas à alteração será usada para modificar a linha de destino; tentativas posteriores de modificar a linha causarão um erro. Isto também poderá ocorrer se os gatilhos no nível de linha fizerem alterações na tabela de destino e as linhas modificadas forem posteriormente modificadas também pelo MERGE. Se a ação repetida for um INSERT, isto causará uma violação de unicidade, enquanto um UPDATE ou DELETE repetido causará uma violação de cardinalidade; este último comportamento é exigido pelo padrão SQL. Isto difere do comportamento histórico do PostgreSQL para junções dentro de comandos UPDATE e DELETE, onde a segunda tentativa de modificar a mesma linha é simplesmente ignorada, assim como as tentativas subsequentes.

Se uma cláusula WHEN omitir uma subcláusula AND, ela se tornará a cláusula final alcançável desse tipo (MATCHED, NOT MATCHED BY SOURCE ou NOT MATCHED [BY TARGET]). Se for especificada uma cláusula WHEN posterior desse tipo, ela será comprovadamente inalcançável e um erro será gerado. Caso não seja especificada nenhuma cláusula final alcançável de qualquer tipo, é possível que nenhuma ação seja tomada para uma linha candidata à alteração.

Por padrão, a ordem em que as linhas são geradas a partir da origem dos dados não é determinada. A consulta_fonte pode ser usada para especificar uma ordem consistente, se necessário, o que pode ser preciso para evitar impasses entre transações simultâneas.

Quando o comando MERGE for executado simultaneamente com outros comandos que modificam a tabela de destino, aplicam-se as regras usuais de isolamento de transações; veja Isolamento de transação para obter uma explicação sobre o comportamento em cada nível de isolamento. Também pode-se considerar usar INSERT ... ON CONFLICT como um comando alternativo que oferece a possibilidade de executar um comando UPDATE no caso de ocorrer um comando INSERT concorrente. Existem diversas diferenças e restrições entre os dois tipos de comando, e elas não são intercambiáveis.

Exemplos

Realizar manutenção em customer_accounts baseado em recent_transactions.

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Tentar inserir um novo item de estoque juntamente com a quantidade em estoque. Se o item já existir, atualizar a quantidade em estoque do item existente. Não permitir entradas com estoque zero. Retornar os detalhes de todas as alterações realizadas.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;

A tabela wine_stock_changes pode ser, por exemplo, uma tabela temporária carregada recentemente no banco de dados.

Atualizar wines com base em uma lista de substituição de vinhos, inserindo linhas para qualquer novo item no estoque, atualizando as entradas de estoque modificadas e excluindo quaisquer vinhos não presentes na nova lista.

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

Conformidade

Este comando está em conformidade com o padrão SQL.

A cláusula WITH, os qualificadores BY SOURCE e BY TARGET para WHEN NOT MATCHED, a ação DO NOTHING e a cláusula RETURNING são extensões ao padrão SQL.

^[150] O comando MERGE atualiza um destino (uma tabela ou visão) usando dados de uma origem (o resultado de uma referência de tabela ou os dados de entrada especificados). As linhas no destino que correspondem aos dados da entrada podem ser excluídas ou atualizadas conforme especificado, e as linhas que não existem no destino podem ser inseridas. Atualizar, excluir ou inserir uma linha em uma visão atualiza, exclui ou insere a linha nas tabelas nas quais a visão se baseia, se nenhum gatilho INSTEAD OF estiver definido nesta visão. IBM Db2 for z/OS - MERGE statement (N. T.)