Alternativa a UPDATE ON DUPLICATE KEY DELETE en MySQL

Lamentablemente, la sentencia expuesta en el título de esta publicación no existe en Mysql. No es posible borrar el registro luego de un UPDATE que genere un duplicado, en la misma sentencia.

La alternativa disponible es utilizar UPDATE IGNORE y luego ejecutar DELETE sobre los registros que fueron ignorados.

Ejemplo

Supongamos que existe la tabla ‘insumos_rubros’ con la asociación entre cada insumo y los rubros a los que pertenece, la cual posee una clave primaria compuesta por los 2 campos: id_insumo y id_rubro.

Al intentar cambiar todos los insumos de un rubro a otro se puede generar un registro duplicado, dado que algunos de ellos podrían haber estado asociados previamente al rubro destino:

mysql> UPDATE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13;
ERROR 1062 (23000): Duplicate entry '9-26' for key 'PRIMARY'

Rápidamente se nos viene a la mente un intento de solución:

mysql> UPDATE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13
    -> ON DUPLICATE KEY DELETE;

el cual hubiera generado un error de sintaxis debido a ‘ON DUPLICATE KEY DELETE’.

Aplicando la alternativa de UPDATE IGNORE y luego DELETE sobre los registros que fueron ignorados

mysql> UPDATE IGNORE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13;
Query OK, 65 rows affected (0.00 sec)

mysql> DELETE FROM insumos_rubros WHERE id_rubro = 13;
Query OK, 3 rows affected (0.00 sec)

logramos el objetivo deseado.

Basado en:

Utilizar los valores indicados en INSERT dentro de ON DUPLICATE KEY UPDATE en MySQL

Una de las alternativas para tratar los registros duplicados en un INSERT de MySQL es utilizar INSERT … ON DUPLICATE KEY UPDATE.  En ciertos casos es necesario actualizar los registros duplicados a partir de los valores que se han indicado en el INSERT, especialmente en una sentencia INSERT de múltiples registros (BULK INSERT).

Para ello existe la función VALUES(columna) que aplicada dentro de la cláusula UPDATE, en el caso de duplicate key, referencia al valor de ‘columna’ que habría sido insertado si no hubiera ocurrido el conflicto.

Ejemplo

Suponiendo que la tabla ‘stock’ posee la cantidad de cada item que se vende en una tienda y tiene un índice UNIQUE por el campo ‘insumo’, al agregar registros:

mysql> INSERT INTO stock (insumo,cant) VALUES ('pañuelos',4), ('corbatas',2)
     -> ON DUPLICATE KEY UPDATE cant=cant+VALUES(cant);

en caso de que ya existiera uno previo para cierto item, se incrementa su stock en la cantidad indicada.

Atención: La función VALUES() solo tiene significado dentro de sentencias INSERT... UPDATE y retorna NULL en cualquier otro caso.

Tratar los registros duplicados en un INSERT de MySQL

Es un sentimiento desagradable el hacer una sentencia INSERT y recibir una respuesta del estilo:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Los registros se consideran duplicados cuando se intenta insertar un registro que tiene un valor igual a uno existente en un campo que posee una PRIMARY KEY o un índice UNIQUE.

Existen 3 alternativas para controlar la forma en que se resolverá la inserción de registros ante la existencia de duplicados. La elección depende de las reglas de negocio principalmente.

INSERT IGNORE

Todos los errores que ocurran durante la inserción serán ignorados y se presentarán como warnings. Se puede decir que se “mantiene el registro existente”.

IGNORE tiene mayor precedencia que el modo estricto, por lo que si ambos aplican y ocurriera un error, se ignoraría.

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> INSERT IGNORE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
Query OK, 0 rows affected (0.00 sec)

se ignora el duplicado, manteniendo los valores previos del registro.

INSERT … ON DUPLICATE KEY UPDATE

Si una fila a insertar fuera a causar un error de duplicación, en su lugar se realiza un UPDATE del registro que existía previamente, modificando uno o más campos a la vez (separando con coma cada asignación) según se indique.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila
  • 2 si la fila existente se modifica
  • 0 si la fila existente se modifica a los valores que ya poseía

Ejemplo

Suponiendo que la tabla ‘paginas_hits’ posee la cantidad de veces que se accedió a una página y tiene un índice UNIQUE por el campo ‘pagina’, al intentar agregar un registro para ‘index.html’:

mysql> INSERT INTO paginas_hits (pagina,hits) VALUES ('index.hml',1)
    -> ON DUPLICATE KEY UPDATE hits=hits+1;

si ya existiera uno previo, lo que sucede es que se actualiza dicho registro incrementando en uno la cantidad de hits.

Es posible que en el cambio a realizar tengas que utilizar los valores indicados en INSERT dentro de ON DUPLICATE KEY UPDATE

Si existe un campo con AUTO_INCREMENT…

al hacer este tipo de INSERT si efectivamente se crea un registro, se genera un nuevo valor para el campo en cuestión. Este se puede obtener a partir de la función LAST_INSERT_ID().

Si el resultado de la sentencia es hacer una actualización del registro, entonces no se puede obtener el id correspondiente con LAST_INSERT_ID(). Para lograr que esta función tenga sentido en este caso, agregamos en el UPDATE para el campo con AUTO_INCREMENT (supongamos que sea id):

id = LAST_INSERT_ID(id)

Así se logra que la función devuelva el mismo valor que se le pasa como argumento y sea recordado y devuelto por la siguiente invocación de LAST_INSERT_ID().

Ejemplo

mysql> INSERT INTO paginas (pagina,hits) VALUES ('index.hml',1)
    -> ON DUPLICATE KEY UPDATE id_pagina=LAST_INSERT_ID(id_pagina), hits=hits+1;

REPLACE

Funciona como INSERT, pero si se detecta un duplicado, se borra el registro existente en la tabla y se realiza la nueva inserción. Se puede decir que se “establece el registro nuevo”, quitando el previo. Al realizar un borrado se desencadenan los triggers asociados al DELETE y los registros vinculados por FOREIGN KEYS podrían borrarse también!

Los pasos que sigue esta sentencia al ejecutarse son:

  1. Intenta insertar el registro
  2. Si falla por duplicate entry de una key
    1. Borra el registro conflictivo de la tabla
    2. Intenta insertar el registro nuevamente

entonces, en caso de fallo por duplicado realiza la secuencia INSERT – DELETE – INSERT.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila (no existía duplicado)
  • 2 si se detecta un duplicado: un registro borrado y uno insertado en su lugar
  • N>2 si hay duplicados debido a múltiples índices UNIQUE por ejemplo

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> REPLACE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
Query OK, 2 rows affected (0.00 sec)

se borra el registro previo y se inserta el nuevo, con los valores indicados.

Si existe un campo con AUTO_INCREMENT…

al hacer el REPLACE en caso de que se detecte un duplicado, se genera un nuevo valor para el campo en cuestión en el registro insertado. Este se puede obtener a partir de la función LAST_INSERT_ID().