Home / Questions / 243630
Accepted
Antonio Ruiz
Asked: 2020-03-07 23:46:17 +0800 CST 2020-03-07 23:46:17 +0800 CST

In MySQL does not update ON DUPLICATE KEY UPDATE [duplicate]

  • 772
INSERT INTO contactos
       (iduser, tipo, codigo, nombre, dni_nif )
VALUES
       ('1','cliente','1','Juan','500000'),
       ('1','cliente','2','Maria','500001'),
       ('2','cliente','1','Sara','500002')
ON DUPLICATE KEY UPDATE 
       codigo = codigo,
       iduser = iduser;

In MySQL : iduser+codigo(unique key)

iduser = code assigned to the client

code = key of the contact of each client

I have the previous query, the first time it is executed it works fine, but if the client decides to re-upload the data ( excel ) to update the existing ones and/or add new clients, I can't get it to update the existing ones.

Where is the problem?

mysql

2 Answers

  1. Best Answer

    Mario.

    From what I see in the query you make, you are trying to modify the code and id_user fields in the update. Which doesn't make sense, since it would assign them the same, values. There, what you have to use are the rest of the fields: type, name and dni_nif, which are the ones you have to update.

    Suppose we have this table:

    +----+-------------+-----------------------------+
| id | name        | team                        |
+----+-------------+-----------------------------+
|  1 | Logan       | x-men                       |
|  2 | Storm       | x-men                       |
|  3 | Beast       | x-men                       |
|  4 | Quicksilver | Brotherhood of Evil Mutants |
+----+-------------+-----------------------------+

    And we want to insert or update (like what you're doing), we have two options:

    1 - REPLACE

    What the replace command does is execute an insert, but if it finds that the record to be inserted has an index that already exists (primary or unique), it deletes it first and inserts the new one. For example:

    REPLACE INTO `mutants` (`id`, `name`, `team`)  VALUES (4, 'Quicksilver', 'x-men');
-- Query OK, 2 rows affected (0.09 sec)

    And the result will be:

    +----+-------------+-------+
| id | name        | team  |
+----+-------------+-------+
|  1 | Logan       | x-men |
|  2 | Storm       | x-men |
|  3 | Beast       | x-men |
|  4 | Quicksilver | x-men |
+----+-------------+-------+

    2 - INSERT ... ON DUPLICATE KEY UPDATE

    For our case it would be:

    INSERT INTO `mutants` (`id`, `name`, `team`)  VALUES (4, 'Quicksilver', 'x-men')   
ON DUPLICATE KEY UPDATE team='inhumans', name='Peter Maximoff';
Query OK, 2 rows affected (0.08 sec)
-- Query OK, 2 rows affected (0.07 sec)

    And the result it gives us:

    +----+----------------+----------+
| id | name           | team     |
+----+----------------+----------+
|  1 | Logan          | x-men    |
|  2 | Storm          | x-men    |
|  3 | Beast          | x-men    |
|  4 | Peter Maximoff | inhumans |
+----+----------------+----------+
    • 3

  2. The problem is that you are using a query INSERTto modify the existing data, and you should use a query UPDATE, since it INSERTwill always try to insert the data into the table, and if there are duplicate keys you get the error you mentioned ( ON DUPLICATE KEY).

    You should run a query similar to the following when trying to modify existing data in a table:

    UPDATE contactos SET tipo = 'cliente', nombre = 'Juan', dni_nif = '500000' 
WHERE iduser = '1' AND codigo = '1'
    • 0