Descripción

UPDATE cambia los valores de las columnas especificadas en todas las filas que cumplen con la condición. Solo es necesario mencionar las columnas que se van a modificar en la cláusula SET; las columnas no modificadas explícitamente conservan sus valores anteriores.

Hay dos formas de modificar una tabla utilizando información contenida en otras tablas de la base de datos: utilizando subconsultas o especificando tablas adicionales en la cláusula FROM. Cuál técnica es más adecuada depende de las circunstancias específicas.

La cláusula opcional RETURNING hace que UPDATE calcule y devuelva los valores basados en cada fila realmente actualizada. Se puede calcular cualquier expresión que use las columnas de la tabla y/o las columnas de otras tablas mencionadas en FROM. Por defecto, se utilizan los nuevos valores (posteriores a la actualización) de las columnas de la tabla, pero también es posible solicitar los valores antiguos (anteriores a la actualización). La sintaxis de la lista RETURNING es idéntica a la de la lista de salida de SELECT.

Debes tener el privilegio UPDATE en la tabla, o al menos en las columnas que se listan para ser actualizadas. También debes tener el privilegio SELECT en cualquier columna cuyos valores se lean en las expressions o en la condition.

Parámetros

Salidas

Al completarse con éxito, un comando UPDATE devuelve una etiqueta de comando de la forma

UPDATE count

El valor count es el número de filas actualizadas, incluyendo las filas coincidentes cuyos valores no cambiaron. Ten en cuenta que el número puede ser menor que el número de filas que coincidieron con la condition cuando las actualizaciones fueron suprimidas por un disparador BEFORE UPDATE. Si count es 0, la consulta no actualizó ninguna fila (esto no se considera un error).

Si el comando UPDATE contiene una cláusula RETURNING, el resultado será similar al de una sentencia SELECT que contiene las columnas y los valores definidos en la lista RETURNING, calculados sobre las filas actualizadas por el comando.

Notas

Cuando hay una cláusula FROM presente, lo que ocurre esencialmente es que la tabla de destino se une a las tablas mencionadas en la lista from_item, y cada fila de salida de la unión representa una operación de actualización para la tabla de destino. Al usar FROM debes asegurarte de que la unión produzca como máximo una fila de salida para cada fila a modificar. En otras palabras, una fila de destino no debe unirse a más de una fila de la otra tabla. Si lo hace, solo se usará una de las filas de la unión para actualizar la fila de destino, pero cuál se usará no es fácilmente predecible.

Debido a esta indeterminación, es más seguro hacer referencia a otras tablas solo dentro de subconsultas, aunque a menudo es más difícil de leer y más lento que usar una unión.

En el caso de una tabla particionada, la actualización de una fila puede hacer que esta deje de cumplir con la restricción de partición de la partición contenedora. In ese caso, si hay alguna otra partición en el árbol de particiones para la cual esta fila cumple con su restricción de partición, la fila se mueve a esa partición. Si no existe tal partición, ocurrirá un error. Detrás de escena, el movimiento de la fila es en realidad una operación de DELETE e INSERT.

Existe la posibilidad de que un UPDATE o DELETE concurrente en la fila que se está moviendo reciba un error de falla de serialización. Supón que la sesión 1 realiza un UPDATE en una clave de partición, y mientras tanto una sesión concurrente 2 para la cual esta fila es visible realiza una operación de UPDATE o DELETE en esta fila. En tal caso, el UPDATE o DELETE de la sesión 2 detectará el movimiento de la fila y generará un error de falla de serialización (que siempre se devuelve con el código SQLSTATE '40001'). Es posible que las aplicaciones deseen reintentar la transacción si esto ocurre. En el caso habitual en que la tabla no está particionada, o donde no hay movimiento de fila, la sesión 2 habría identificado la fila recién actualizada y habría llevado a cabo el UPDATE/DELETE en esta nueva versión de la fila.

Ten en cuenta que aunque las filas se pueden mover desde particiones locales a una partición de tabla foránea (siempre que el conector de datos foráneos admita el enrutamiento de tuplas), no se pueden mover desde una partición de tabla foránea a otra partición.

Un intento de mover una fila de una partición a otra fallará si se encuentra que una clave foránea hace referencia directamente a un ancestro de la partición de origen que no es el mismo ancestro mencionado en la consulta UPDATE.

Ejemplos

Cambia la palabra Drama a Dramatic en la columna kind de la tabla films:

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

Ajusta las entradas de temperatura y restablece la precipitación a su valor por defecto en una fila de la tabla 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';

Realiza la misma operación y devuelve las entradas actualizadas, y el antiguo valor de precipitación:

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;

Usa la sintaxis alternativa de lista de columnas para realizar la misma actualización:

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

Incrementa el recuento de ventas del vendedor que gestiona la cuenta de Acme Corporation, utilizando la sintaxis de la cláusula FROM:

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

Realiza la misma operación, utilizando una subconsulta en la cláusula WHERE:

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

Actualiza los nombres de contacto en una tabla de cuentas para que coincidan con los vendedores asignados actualmente:

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

Se podría lograr un resultado similar con una unión:

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

Sin embargo, la segunda consulta puede dar resultados inesperados si employees.id no es una clave única, mientras que la primera consulta garantiza que se generará un error si hay múltiples coincidencias de id. Además, si no hay ninguna coincidencia para una entrada de particular de accounts.sales_person, la primera consulta establecerá los campos de nombre correspondientes a NULL, mientras que la segunda consulta no actualizará esa fila en absoluto.

Actualiza las estadísticas en una tabla de resumen para que coincidan con los datos actuales:

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

Intenta insertar un nuevo artículo de stock junto con la cantidad. Si el artículo ya existe, actualiza en su lugar el recuento de stock del artículo existente. Para hacer esto sin que falle toda la transacción, usa puntos de salvaguarda:

BEGIN;
-- otras operaciones
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Asume que lo anterior falla debido a una violación de clave única,
-- por lo que ahora emitimos estos comandos:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continúa con otras operaciones y, finalmente,
COMMIT;

Cambia la columna kind de la tabla films en la fila sobre la cual está posicionado actualmente el cursor c_films:

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

Las actualizaciones que afectan a muchas filas pueden tener efectos negativos en el rendimiento del sistema, como el crecimiento excesivo de la tabla («table bloat»), un mayor retraso en la réplica y una mayor contención de bloqueos. En tales situaciones, puede tener sentido realizar la operación en lotes más pequeños, posiblemente con una operación de VACUUM en la tabla entre lotes. Aunque no existe una cláusula LIMIT para UPDATE, es posible obtener un efecto similar mediante el uso de una Expresión de Tabla Común (CTE, «Common Table Expression») y una autounión. Con el método de acceso de tabla estándar de PostgreSQL, una autounión en la columna del sistema ctid es muy 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 deberá repetirse hasta que no queden filas por actualizar. (Este uso de ctid solo es seguro porque la consulta se ejecuta repetidamente, evitando el problema de los ctids modificados). El uso de una cláusula ORDER BY permite que el comando priorice qué filas se actualizarán; también puede evitar bloqueos mutuos con otras operaciones de actualización si utilizan el mismo orden. Si la contención de bloqueos es una preocupación, entonces se puede agregar SKIP LOCKED a la CTE para evitar que múltiples comandos actualicen la misma fila. Sin embargo, luego se necesitará un UPDATE final sin SKIP LOCKED ni LIMIT para asegurar que no se omitiera ninguna fila coincidente.

Compatibilidad

Este comando cumple con el estándar SQL, excepto que las cláusulas FROM y RETURNING son extensiones de PostgreSQL, al igual que la capacidad de usar WITH con UPDATE.

Algunos otros sistemas de bases de datos ofrecen una opción FROM en la cual se supone que la tabla de destino debe listarse nuevamente dentro de FROM. No es así como PostgreSQL interpreta FROM. Ten cuidado al portar aplicaciones que utilicen esta extensión.

De acuerdo con el estándar, el valor fuente para una sublista entre paréntesis de nombres de columnas de destino puede ser cualquier expresión con valor de fila que produzca el número correcto de columnas. PostgreSQL solo permite que el valor fuente sea un constructor de filas o un sub-SELECT. El valor actualizado de una columna individual se puede especificar como DEFAULT en el caso del constructor de filas, pero no dentro de un sub-SELECT.