Descrição

O comando UPDATE atualiza os valores das colunas especificadas em todas as linhas que atendem à condição. Apenas as colunas a serem atualizadas precisam ser mencionadas na cláusula SET; as colunas não atualizadas explicitamente mantêm seus valores anteriores.

Existem duas maneiras de atualizar uma tabela usando informações contidas em outras tabelas do banco de dados: usando sub-seleções ou especificando tabelas adicionais na cláusula FROM. Qual técnica é a mais apropriada depende das circunstâncias específicas.

A cláusula opcional RETURNING faz com que o comando UPDATE calcule e retorne valor(es) com base em cada linha realmente atualizada. Pode ser computada qualquer expressão usando as colunas da tabela, e/ou colunas de outras tabelas mencionadas na cláusula FROM. Por padrão, são utilizados os novos valores (pós-atualização) das colunas da tabela, mas também é possível solicitar os valores antigos (pré-atualização). A sintaxe da lista RETURNING é idêntica à da lista de saída do comando SELECT.

É necessário ter o privilégio UPDATE na tabela, ou pelo menos na(s) coluna(s) que estão listadas para serem atualizadas. Também é necessário ter o privilégio SELECT em qualquer coluna cujos valores sejam lidos pelas expressões ou pela condição.

Parâmetros

Saídas

Após a conclusão bem-sucedida, o comando UPDATE retorna uma etiqueta de comando na forma

UPDATE contador

O contador é o número de linhas atualizadas. Note que o número pode ser menor que o número de linhas que corresponderam à condição, quando atualizações forem suprimidas por um gatilho BEFORE UPDATE. Se o contador for igual a 0, nenhuma linha foi atualizada pelo comando (isto não é considerado um erro).

Se o comando UPDATE contiver uma cláusula RETURNING, o resultado será semelhante ao de um comando SELECT contendo as colunas e valores definidos na lista da cláusula RETURNING, computadas sobre a(s) linha(s) atualizada(s) pelo comando.

Notas

Quando a cláusula FROM está presente, o que acontece essencialmente é que a tabela indicada por nome_da_tabela é juntada às tabelas mencionadas na lista de item_FROM, e cada linha de saída da junção representa uma operação de atualização para a tabela indicada por nome_da_tabela Ao usar FROM, deve-se garantir que a junção produza no máximo uma linha de saída para cada linha a ser modificada. Em outras palavras, uma linha da tabela indicada por nome_da_tabela não deve se juntar a mais de uma linha da(s) outra(s) tabela(s). Em caso afirmativo, apenas uma das linhas da junção será usada para atualizar a linha da tabela indicada por nome_da_tabela, mas qual delas será usada não é previsível.

Devido a esta indeterminação, referenciar outras tabelas somente dentro de sub-seleções é mais seguro, embora muitas vezes mais difícil de ler e mais lento do que usar uma junção.

No caso de uma tabela particionada, a atualização da linha pode fazer com que esta linha não satisfaça mais a restrição de partição, da partição que a contém. Neste caso, havendo alguma outra partição na árvore de partição para a qual esta linha satisfaça sua restrição de partição, a linha será movida para esta partição. Não havendo tal partição, ocorrerá um erro. Por trás, a movimentação da linha é na verdade uma operação de DELETE seguida de INSERT.

Existe a possibilidade de que um comando UPDATE ou DELETE simultâneo na linha que está sendo movida receba um erro de falha de serialização. Suponha que a sessão 1 esteja executando um UPDATE em uma chave de partição e, enquanto isto, uma sessão simultânea 2 para a qual esta linha está visível executa uma operação de UPDATE ou DELETE nesta linha. Neste caso, o comando UPDATE ou DELETE da sessão 2 detectará o movimento da linha, gerando um erro de falha de serialização (que sempre retorna com o código SQLSTATE '40001'). As aplicações podem desejar repetir a transação se isto ocorrer. No caso usual onde a tabela não é particionada, ou onde não há movimentação de linha, a sessão 2 teria identificado a linha recém-atualizada e executado o comando UPDATE/DELETE nesta nova versão da linha.

Note que, embora as linhas possam ser movidas de partições locais para uma partição de tabela estrangeira (desde que o empacotador de dados estrangeiros suporte roteamento de tupla), elas não podem ser movidas de uma partição de tabela estrangeira para outra partição.

Uma tentativa de mover uma linha de uma partição para outra irá falhar se for encontrada uma chave estrangeira que faça referência direta a um ancestral da partição de origem que não seja o mesmo que o ancestral mencionado no comando UPDATE.

Exemplos

Mudar a palavra Drama para Dramatic na coluna kind da tabela films:

UPDATE films
  SET kind = 'Dramatic'
  WHERE kind = 'Drama';

Ajustar as entradas de temperatura e redefinir a precipitação para seu valor padrão em uma linha da tabela weather:

UPDATE weather
  SET temp_lo = temp_lo+1,
      temp_hi = temp_lo+15,
      prcp = DEFAULT
  WHERE city = 'San Francisco'
    AND date = '2003-07-03';

Realizar a mesma operação retornando os valores atualizados, bem como o valor antigo da precipitação:

UPDATE weather
  SET temp_lo = temp_lo+1,
      temp_hi = temp_lo+15,
      prcp = DEFAULT
  WHERE city = 'San Francisco'
    AND date = '2003-07-03'
  RETURNING temp_lo,
            temp_hi,
            prcp,
            old.prcp AS old_prcp;

Uso da sintaxe alternativa de lista de colunas para fazer a mesma atualização:

UPDATE weather
  SET (temp_lo, temp_hi, prcp) =
      (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco'
    AND date = '2003-07-03';

Incrementar a contagem de vendas do vendedor que gerencia a conta da “Acme Corporation”, usando a sintaxe da cláusula FROM:

UPDATE employees
  SET sales_count = sales_count + 1
  FROM accounts
  WHERE accounts.name = 'Acme Corporation'
    AND employees.id = accounts.sales_person;

Realizar a mesma operação, usando uma subseleção na cláusula WHERE:

UPDATE employees
  SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person
     FROM accounts
     WHERE name = 'Acme Corporation');

Atualizar os nomes dos contatos na tabela accounts, para corresponder aos vendedores atuais:

UPDATE accounts
  SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM employees
     WHERE employees.id = accounts.sales_person);

Um resultado semelhante poderia ser obtido com uma junção:

UPDATE accounts
  SET contact_first_name = first_name,
      contact_last_name = last_name
  FROM employees
  WHERE employees.id = accounts.sales_person;

Entretanto, a segunda consulta pode fornecer resultados inesperados se employees.id não for uma chave de unicidade, enquanto a primeira consulta certamente irá produzir um erro se houver vários id com correspondência. Além disso, se não houver correspondência para uma determinada entrada accounts.sales_person, a primeira consulta irá definir os campos de nome correspondentes como NULL, enquanto a segunda consulta não atualizará esta linha de forma alguma.

Atualizar as estatísticas em uma tabela de resumo para corresponder aos dados correntes:

UPDATE summary s
  SET (sum_x, sum_y, avg_x, avg_y) =
    (SELECT sum(x), sum(y), avg(x), avg(y)
     FROM data d
     WHERE d.group_id = s.group_id);

Tentar inserir um novo item de estoque junto com a quantidade em estoque. Se o item já existir, atualizar a contagem de estoque do item existente. Para fazer isto sem falhar toda a transação, deve ser usado ponto de salvamento:

BEGIN;
-- outras operações
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Supondo que o comando acima falhe devido a uma
-- violação de chave de unicidade, então agora
-- deverão ser executados estes comandos:
ROLLBACK TO sp1;
UPDATE wines
  SET stock = stock + 24
  WHERE winename = 'Chateau Lafite 2003';
-- continuar com outras operações e, finalmente,
COMMIT;

Atualizar a coluna kind da tabela films na linha em que o cursor c_films está posicionado atualmente:

UPDATE films
  SET kind = 'Dramatic'
  WHERE CURRENT OF c_films;

Atualizações que afetam muitas linhas podem ter efeitos negativos no desempenho do sistema, como inchaço da tabela, aumento da latência de réplica e aumento da disputa por bloqueios. Nessas situações, pode ser sensato realizar a operação em lotes menores, possivelmente se considerando uma operação de VACUUM entre os lotes. Embora não exista uma cláusula LIMIT para o comando UPDATE, é possível obter um efeito semelhante através do uso de Expressões de tabela comuns (CTEs) e uma autojunção. Com o método de acesso à tabela padrão do PostgreSQL, uma autojunção na coluna do sistema ctid é muito eficiente:

WITH exceeded_max_retries AS (
  SELECT w.ctid FROM work_item AS w
    WHERE w.status = 'active'
      AND w.num_retries > 10
    ORDER BY w.retry_timestamp
    FOR UPDATE
    LIMIT 5000
)
UPDATE work_item SET status = 'failed'
  FROM exceeded_max_retries AS emr
  WHERE work_item.ctid = emr.ctid;

Este comando precisará ser repetido até que não haja mais linhas a serem atualizadas. O uso de uma cláusula ORDER BY permite que o comando priorize quais linhas serão atualizadas; e também pode evitar impasses com outras operações de atualização caso elas usem a mesma ordem. Se a contenção de bloqueio for uma preocupação, então poderá ser adicionado SKIP LOCKED ao CTE para impedir que vários comandos atualizem a mesma linha. Entretanto, será necessário um comando final UPDATE sem SKIP LOCKED ou LIMIT para garantir que nenhuma linha com correspondência tenha sido ignorada.

Conformidade

Este comando está em conformidade com o padrão SQL, exceto que as cláusulas FROM e RETURNING são extensões do PostgreSQL, assim como a capacidade de usar WITH com UPDATE.

Alguns outros sistemas de banco de dados oferecem uma cláusula FROM na qual a tabela de destino deve ser listada novamente dentro do FROM. Não é assim que PostgreSQL interpreta a cláusula FROM. Tenha cuidado ao portar aplicações que usam esta extensão.

Segundo o padrão, o valor de origem para uma sublista entre parênteses de nomes de colunas de destino pode ser qualquer expressão de valor de linha que forneça o número correto de colunas. O PostgreSQL permite apenas que o valor de origem seja um construtor de linha ou um sub-SELECT. O valor atualizado de uma coluna individual pode ser especificado como DEFAULT no caso do construtor de linha, mas não em um sub-SELECT.