CODEWITHSUNDEEP
mysqlprimary-key
Guy
Guy

Reputation: 91

MySQL: ON DUPLICATE KEY UPDATE works not with primary key

I want to update a MySQL row, when the primary already exists. The problem is that the query updates nothing on second execution.

The table-structure:

mysql> DESCRIBE Testtable;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| test_id      | int(11)      | NO   | PRI | NULL    |       |
| test_varchar | varchar(128) | NO   |     | NULL    |       |
| test_text    | text         | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The query:

INSERT INTO `Testtable`(`test_id`, `test_varchar`, `test_text`) VALUES ('1', 'hello', 'hello world!') ON DUPLICATE KEY UPDATE `test_id` = '1';

Result of first execution:

Query OK, 1 row affected (0.01 sec)

Result of second execution:

Query OK, 0 rows affected (0.00 sec)

I don't understand why are 0 rows affected, because in the manual it says:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

Yeah, I have a primary key as you can see in the structure, but I don't understand why are 0 rows affected. How to optimise the query that it will work? Do I need to specify all variables again in the ON DUPLICATE KEY UPDATE-part?

Upvotes: 0

Views: 1629

Answers (1)

Annemieke Buijs
Annemieke Buijs

Reputation: 80

According to https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

Upvotes: 3

Related Questions