Objetivo

Use a instrução UPDATE para alterar os valores existentes em uma tabela ou na tabela base de uma visão ou a tabela mestre de uma visão materializada.

Tópicos adicionais

• Prérequisitos

• Sintaxe

• Semantics

• Examples

Pré-requisitos

Para para atualizar os valores em uma tabela, a tabela deve estar em seu próprio esquema ou você deve ter o UPDATE privilégio de objeto na tabela.

Para você atualizar valores na tabela base de uma visualização:

• Você deve ter o UPDATE privilégio de objeto na visualização e

• Quem possui o esquema que contém a visualização deve ter o UPDATE privilégio de objeto na tabela base.

O UPDATE ANY TABLE privilégio de sistema também permite que você atualize valores em qualquer mesa ou na mesa base de qualquer visualização.

Você também deve ter o privilégio de objeto SELECT no objeto que deseja atualizar se:

• O objeto está em um banco de dados remoto ou

• O parâmetro de inicialização SQL92_SECURITY é definido como TRUE e a operação UPDATE faz referência a colunas de tabela, como as colunas em uma where_clause.

Sintaxe

update :: =

Descrição da ilustração update.gif

(DML_table_expression_clause :: =, update_set_clause :: =, where_clause :: =, return_clause :: =, error_logging_clause :: =)

DML_table_expression_clause :: =

Descrição da ilustração DML_table_expression_clause.gif

(subquery :: = – parte de SELECT, subquery_restriction_clause :: =, table_collection_expression :: =)

subquery_restriction_clause :: =

Descrição da ilustração subquery_restriction_clause.gif

table_collection_expression :: =

Descrição da ilustração table_collection_expression.gif

update_set_clause :: =

Descrição da ilustração update_set_clause. gif

where_clause :: =

Descrição da ilustração where_clause.gif

return_clause :: =

Descrição da ilustração return_clause.gif

error_logging_clause :: =

Descrição da ilustração error_logging_clause.gif

Semântica

dica

Especifique um comentário que passa instruções ao otimizador sobre a escolha de um plano de execução para a instrução.

Você pode colocar uma dica paralela imediatamente após a UPDATE palavra-chave para paralelizar ambos varredura mentirosa e operações UPDATE.

DML_table_expression_clause

A cláusula ONLY se aplica apenas a visualizações. Especifique a sintaxe ONLY se a visão na cláusula UPDATE for uma visão que pertence a uma hierarquia e você não deseja atualizar as linhas de nenhuma de suas subvisualizações.

esquema

Especifique o esquema que contém o objeto a ser atualizado. Se você omitir schema, o banco de dados assume que o objeto está em seu próprio esquema.

tabela | ver | materialized_view | subquery

Especifique o nome da tabela, visão, visão materializada ou as colunas retornadas por uma subconsulta a ser atualizada. A emissão de uma instrução UPDATE contra uma tabela dispara quaisquer UPDATE gatilhos associados à tabela.

• Se você especificar view, o banco de dados atualizará a tabela base da exibição.Você não pode atualizar uma visualização, exceto com INSTEAD OF acionadores se a consulta de definição da visualização contiver uma das seguintes construções:

Um operador de conjunto ADISTINCToperador Uma função agregada ou analítica AGROUPBY,ORDERBY,MODEL,CONNECTBY, ouSTARTWITHcláusula Uma expressão de coleção em umSELECTlista Uma subconsulta em umaSELECTlista Uma subconsulta designadaWITH READ ONLYJunções, com algumas exceções , conforme documentado no Guia do Administrador do Banco de Dados Oracle

• Você não pode atualizar mais de uma tabela base por meio de uma visualização.

• Além disso, se o visualização foi criada com WITH CHECK , então você pode atualizar a visualização apenas se os dados resultantes satisfizerem a consulta de definição da visualização.

• Se table ou a tabela base de view contém uma ou mais colunas de índice de domínio, então esta instrução executa a rotina de atualização de tipo de índice apropriada.

• Você não pode atualizar linhas em uma visualização materializada somente leitura. Se você atualizar linhas em uma visão materializada gravável, o banco de dados atualizará as linhas da tabela de contêiner subjacente. No entanto, as atualizações são substituídas na próxima operação de atualização. Se você atualizar as linhas em uma visão materializada atualizável que faz parte de um grupo de visão materializada, o banco de dados também atualiza as linhas correspondentes na tabela mestre.

PARTIÇÃO | SUBPARTIÇÃO

Especifique o nome da partição ou subpartição em table direcionado para atualizações. Você não precisa especificar o nome da partição ao atualizar os valores em uma tabela particionada. No entanto, em alguns casos, especificar o nome da partição pode ser mais eficiente do que um complicado where_clause .

dblink

Especifique um nome completo ou parcial de um link de banco de dados para um banco de dados remoto onde t O objeto está localizado. Você pode usar um link de banco de dados para atualizar um objeto remoto apenas se estiver usando a funcionalidade distribuída do banco de dados Oracle.

Se você omitir dblink,, o banco de dados assume que o objeto é no banco de dados local.

subquery_restriction_clause

Use subquery_restriction_clause para restringir a subconsulta de uma das seguintes maneiras:

COM SOMENTE LEITURA Especifique WITH READ ONLY para indicar que a tabela ou exibição não pode ser atualizada.

COM OPÇÃO DE VERIFICAÇÃO Especifique WITH CHECK OPTION para indicar que o banco de dados Oracle proíbe qualquer alteração na tabela ou exibição que produziria linhas que não estão incluídas na subconsulta. Quando usado na subconsulta de uma instrução DML, você pode especificar esta cláusula em uma subconsulta na cláusula FROM, mas não na subconsulta na WHERE cláusula.

Restrição CONSTRAINT Especifique o nome da restrição CHECK OPTION. Se você omitir esse identificador, o Oracle atribuirá automaticamente à restrição um nome no formato SYS_C n, onde n é um número inteiro que torna o nome de restrição exclusivo no banco de dados.

table_collection_expression

O table_collection_expression permite informar ao Oracle que o valor de collection_expression deve ser tratado como uma tabela para fins de consulta e operações DML. O collection_expression pode ser uma subconsulta, uma coluna, uma função ou um construtor de coleção. Independentemente de sua forma, ele deve retornar um valor de coleção – ou seja, um valor cujo tipo é tabela aninhada ou varray. Este processo de extrair os elementos de uma coleção é chamado de desaninhamento da coleção.

O sinal de adição opcional (+) é relevante se você estiver juntando a expressão TABLE com o pai tabela. O + cria uma junção externa dos dois, para que a consulta retorne linhas da tabela externa, mesmo se a expressão de coleção for nula.

Você pode usar um table_collection_expression para atualizar as linhas de uma tabela com base nas linhas de outra tabela. Por exemplo, você pode acumular quatro tabelas de vendas trimestrais em uma tabela de vendas anuais.

t_alias

Especifique um nome de correlação (alias) para a tabela, visão ou subconsulta a ser referenciada em outra parte da declaração. Este alias é necessário se DML_table_expression_clause fizer referência a quaisquer atributos de tipo de objeto ou métodos de tipo de objeto.

Restrições na DML_table_expression_clause Esta cláusula está sujeita às seguintes restrições:

• Você não pode executar esta instrução se table ou a tabela base de view contém quaisquer índices de domínio marcados como IN_PROGRESS ou FAILED.

• Você não pode inserir em uma partição se alguma partição de índice afetada estiver marcada como UNUSABLE.

• Você não pode especificar o order_by_clause na subconsulta de DML_table_expression_clause.

• Se você especificar um índice, partição de índice ou subpartição de índice que foi marcado UNUSABLE, a instrução UPDATE falhará, a menos que o SKIP_UNUSABLE_INDEXES parâmetro de sessão foi definido como TRUE.

update_set_clause

A update_set_clause permite definir valores de coluna.

coluna

Especifique o nome de uma coluna do objeto que deve ser atualizado. Se você omitir uma coluna da tabela de update_set_clause, o valor dessa coluna permanecerá inalterado.

Se column refere-se a um atributo de objeto LOB, então você deve primeiro inicializá-lo com um valor vazio ou nulo. Você não pode atualizá-lo com um literal. Além disso, se você estiver atualizando um valor de LOB usando algum método diferente de uma instrução UPDATE SQL direta, você deve primeiro bloquear a linha que contém o LOB. Consulte for_update_clause para obter mais informações.

Se column fizer parte da chave de particionamento de uma tabela particionada, UPDATE irá falhar se você alterar um valor na coluna que moveria a linha para uma partição ou subpartição diferente, a menos que você habilite o movimento da linha. Por favor, consulte row_movement_clause de CREATE TABLE ou ALTER TABLE.

Além disso, se column fizer parte do chave de particionamento de uma tabela particionada por lista, UPDATE falhará se você especificar um valor para a coluna que ainda não existe em partition_value lista de uma das partições.

subconsulta

Especifique uma subconsulta que retorne exatamente uma linha para cada linha atualizada.

• Se você especifica apenas uma coluna em update_set_clause, então a subconsulta pode retornar apenas um valor.

• Se você especificar várias colunas em update_set_clause, então a subconsulta deve retornar tantos valores quanto as colunas especificadas.

• Se a subconsulta não retornar nenhuma linha, o coluna é atribuída a um nulo.

• Se este subquery se refere a objetos remotos, então o UPDATE pode ser executada em paralelo, desde que o referen ce não retorna para um objeto no banco de dados local. No entanto, se subquery em DML_table_expression_clause se referir a qualquer objeto remoto, então o UPDATE a operação será executada em série sem notificação.

Você pode usar flashback_query_clause na subconsulta para atualizar table com dados anteriores. Consulte a flashback_query_clause de SELECT para obter mais informações sobre esta cláusula.

expr

Especifique uma expressão que resolva para o novo valor atribuído à coluna correspondente.

PADRÃO Especifique DEFAULT para definir a coluna com o valor especificado anteriormente como padrão valor para a coluna. Se nenhum valor padrão para a coluna correspondente tiver sido especificado, o banco de dados definirá a coluna como nula.

Restrição na atualização para valores padrão Você não pode especificar DEFAULT se você está atualizando uma visualização.

Cláusula VALUE

A cláusula VALUE permite que você especifique a linha inteira de uma tabela de objeto.

Restrição na cláusula VALUE Você pode especificar esta cláusula apenas para uma tabela de objeto.

where_clause

A where_clause permite que você restrinja as linhas atualizadas àquelas para as quais a condition é verdade. Se você omitir esta cláusula, o banco de dados atualizará todas as linhas na tabela ou visualização. Consulte o Capítulo 7, “Condições” para obter a sintaxe de condition.

O where_clause determina as linhas em quais valores são atualizados. Se você não especificar where_clause, todas as linhas serão atualizadas. Para cada linha que satisfaça a where_clause, as colunas à esquerda do operador de igualdade (=) em update_set_clause são definidas para os valores das expressões correspondentes à direita do operador. As expressões são avaliadas conforme a linha é atualizada.

return_clause

A cláusula return recupera as linhas afetadas por uma instrução DML. Você pode especificar esta cláusula para tabelas e visualizações materializadas e para visualizações com uma única tabela de base.

Ao operar em uma única linha, uma instrução DML com um returning_clause pode recuperar expressões de coluna usando a linha afetada, rowid e REFs para a linha afetada e armazená-los em variáveis de host ou variáveis PL / SQL.

Ao operar em várias linhas, uma instrução DML com returning_clause armazena valores de expressões, rowids e REFs envolvendo as linhas afetadas em matrizes de ligação.

expr Cada item na lista expr deve ser uma sintaxe de expressão válida.

INTO O INTO cláusula indica que os valores das linhas alteradas devem ser armazenados nas variáveis especificadas na lista data_item.

data_item Cada data_item é uma variável de host ou variável PL / SQL que armazena o valor expr recuperado.

Para cada expressão na lista RETURNING, você deve especificar uma variável PL / SQL compatível com o tipo correspondente ou uma variável de host em INTO list.

Restrições As seguintes restrições se aplicam à cláusula RETURNING:

• O expr é restrito da seguinte forma:

  • Para UPDATE e DELETE instruções cada expr deve ser uma expressão simples ou uma expressão de função agregada de conjunto único. Você não pode combinar expressões simples e expressões de função de agregação de conjunto único no mesmo returning_clause. Para INSERT instruções, cada expr deve ser uma expressão simples. As funções agregadas não são suportadas em uma INSERT instrução RETURNING cláusula.

  • Única- definir expressões de função agregada não podem incluir a DISTINCT palavra-chave.

• Se o expr lista contém uma coluna de chave primária ou outra coluna NOT NULL, então a instrução de atualização falhará se a tabela tiver um BEFORE UPDATE gatilho definido nele.

• Você não pode especificar o returning_clause para uma inserção de múltiplas tabelas.

• Você não pode usar esta cláusula com DML paralelo ou com objetos remotos.

• Você não pode recuperar LONG tipos com esta cláusula.

• Você não pode especificar esta cláusula para uma visão na qual um INSTEAD OF gatilho foi definido.

error_logging_clause / p>

A error_logging_clause tem o mesmo comportamento em uma instrução UPDATE e em uma instrução INSERT. Consulte a INSERT instrução error_logging_clause para obter mais informações.

Exemplos

Atualizando uma tabela: exemplos A seguir declaração dá comissões nulas a todos os funcionários com o cargo SH_CLERK:

UPDATE employees SET commission_pct = NULL WHERE job_id = "SH_CLERK";

A declaração a seguir promove Douglas Grant a gerente do Departamento 20 com um aumento de $ 1.000:

UPDATE employees SET job_id = "SA_MAN", salary = salary + 1000, department_id = 120 WHERE first_name||" "||last_name = "Douglas Grant"; 

A declaração a seguir aumenta o salário de um funcionário na employees tabela no banco de dados remote:

UPDATE employees@remote SET salary = salary*1.1 WHERE last_name = "Baer";

O próximo exemplo mostra as seguintes construções sintáticas de UPDATE declaração:

• Ambas as formas do update_set_clause juntas em uma única declaração

• Uma subconsulta correlacionada

• Uma where_clause para limitar as linhas atualizadas

A instrução UPDATE anterior executa as seguintes operações:

• Atualiza apenas os funcionários que trabalham em Genebra ou Munique (locais 2900 e 2700)

• Conjuntos department_id para esses funcionários ao department_id correspondente a Bombaim (location_id 2100)

• Define o salário de cada funcionário em 1,1 vezes o salário médio de seu departamento

• Define a comissão de cada funcionário em 1,5 vezes a comissão média de seu departamento

Atualizando uma partição: exemplo O exemplo a seguir atualiza valores em uma única partição da tabela sales:

UPDATE sales PARTITION (sales_q1_1999) s SET s.promo_id = 494 WHERE amount_sold > 1000;

Atualizando uma tabela de objeto: exemplo A instrução a seguir cria duas tabelas de objeto, people_demo1 e people_demo2 , do objeto people_typ criado em Coleções de tabelas: Exemplos. O exemplo mostra como atualizar uma linha de people_demo1 selecionando uma linha de people_demo2:

O exemplo usa o VALUE função de referência do objeto na cláusula SET e na subconsulta.

Atualização correlacionada: exemplo para um exemplo que usa uma subconsulta correlacionada para atualizar as linhas da tabela aninhada, consulte “Coleções de tabelas: exemplos”.

Usando a cláusula RETURNING durante UPDATE: Exemplo O exemplo a seguir retorna valores da linha atualizada e armazena o resultado em PL Variáveis / SQL bnd1, bnd2, bnd3:

O o exemplo a seguir mostra que você pode especificar uma função de agregação de conjunto único na expressão da cláusula de retorno:

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1;