CODEWITHSUNDEEP
mysqlstored-proceduresinsert
Pietro Doninelli
Pietro Doninelli

Reputation: 21

mysql update throwing Error Code: 1062. Duplicate entry

I´m getting an error on the table 'telefono' : Error Code: 1062. Duplicate entry '84-1' for key 'PRIMARY' when the stored procedure is called and they are in the opposite order in the table.

For example if i do call updateProvider("1", "Terra1", "Ana", "Mora", "Escobar", "31312323", "11111111"); and the numbers 31312323 and 11111111 already have an entry in the table telefono_proveedor in the opposite order it throws the error.

The error corresponds to this update

UPDATE telefono_proveedor SET id_telefono = var_id_phone1
        WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
        WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;

SQL of the table :

CREATE TABLE `telefono` (
  `id_telefono` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tipo` enum('casa','movil','oficina') NOT NULL,
  `numero` varchar(20) NOT NULL,
  PRIMARY KEY (`id_telefono`),
  UNIQUE KEY `id_telefono_UNIQUE` (`id_telefono`),
  UNIQUE KEY `numero_UNIQUE` (`numero`),
  UNIQUE KEY `numero` (`numero`)
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8;

This is the diagram of the table

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateProvider`(
IN var_id_prov INT(10), IN var_provider VARCHAR(20),
IN var_rep_name VARCHAR(20), IN var_rep_last_name VARCHAR(20), 
IN var_rep_last_name2 VARCHAR(20), IN var_phone1 VARCHAR(20), 
IN var_phone2 VARCHAR(20)
)
BEGIN

DECLARE old_id_phone1 INT(10);
DECLARE old_id_phone2 INT(10);
DECLARE var_id_phone1 INT(10);
DECLARE var_id_phone2 INT(10);

SELECT id_telefono INTO old_id_phone1 FROM telefono_proveedor
    WHERE id_proveedor = var_id_prov ORDER BY id_telefono ASC LIMIT 1;
SELECT id_telefono INTO old_id_phone2 FROM telefono_proveedor
    WHERE id_proveedor = var_id_prov ORDER BY id_telefono ASC LIMIT 1,1;

INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone1);
INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone2);

SELECT id_telefono INTO var_id_phone1 FROM telefono WHERE numero = var_phone1;
SELECT id_telefono INTO var_id_phone2 FROM telefono WHERE numero = var_phone2;

UPDATE telefono_proveedor SET id_telefono = var_id_phone1
    WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
    WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;

UPDATE proveedor SET nombre_proveedor = var_provider, nombre_representante = var_rep_name, 
    apellido1_representante = var_rep_last_name, apellido2_representante = var_rep_last_name2
    WHERE id_proveedor = var_id_prov;

END

Upvotes: 1

Views: 1986

Answers (2)

Pietro Doninelli
Pietro Doninelli

Reputation: 21

Solved by changing the updates into delete and then insert. The error happened because i was trying to update a record with values that already were in another record which belong to a primary key. Now with delete and insert the order does not matter as all the records will be deleted an the inserted again.

Original:

UPDATE telefono_proveedor SET id_telefono = var_id_phone1
        WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
        WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;

Fixed with:

DELETE FROM telefono_proveedor WHERE id_proveedor = var_id_prov;
INSERT INTO telefono_proveedor (id_telefono, id_proveedor) VALUES (var_id_phone1, var_id_prov);
INSERT INTO telefono_proveedor (id_telefono, id_proveedor) VALUES (var_id_phone2, var_id_prov);

Upvotes: 1

JRD
JRD

Reputation: 1987

Why are you modifying primary keys like this? You are bound to run into issues with that much volatility with your primary keys.

It seems you are trying to update the primary to a value that already exists.

-- This will create a brand new row with a new/unique id_telefono.
INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone1);

-- You are selecting the id_telefono that use just inserted.
SELECT id_telefono INTO var_id_phone1 FROM telefono WHERE numero = var_phone1;

-- Now you are updating another row to the id_telefono that you just inserted,
-- resulting in the duplicate primary key error.
/*Actualizo la tabla telefono_proveedor */
UPDATE telefono_proveedor SET id_telefono = var_id_phone1
    WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;

I would suggest you leave the primary keys alone, and do not modify them once created. Modify other columns in your table to capture your business requirement.

Upvotes: 1

Related Questions