Hello. I have this question. There is a table with fields id and value . The id - NOT NULL PRIMARY KEY, AUTO_INCREMENT. field id - NOT NULL PRIMARY KEY, AUTO_INCREMENT.

1) Is it possible to somehow tell MySQL that when adding a new line with a repeating id it automatically updates the already existing line.

2) The same, but when the primary key is several fields at once.

  • Why it is impossible to do instead of insert update and specify where id = value? - wind
  • It is simply not known whether such an id exists or not. - alex_90
  • A little off topic, but anyway I will write. As far as I remember, if the field is specified as Primary Key, then NOT NULL is not necessary to write - this is already included in the Primay Key constraint - Sasha121

1 answer 1

Use the INSERT ... ON DUPLICATE KEY UPDATE :

 INSERT INTO table (id,value) VALUES (1,2) ON DUPLICATE KEY UPDATE value = VALUES(value);

see the documentation

  • @renegator, now you can do the same, but only in Russian. Comment on the answer please. - alex_90
  • The people, finally explain what is written in this article. Or am I alone do not speak English)) ?? - alex_90
  • firstly, it is impossible to explicitly assign a value to a field with an autoincrement — the server simply curses secondly. in Russian, the MySQL manual says the following: if you specify ON DUPLICATE KEY UPDATE, and the inserted row causes duplication of a unique index or primary key, then UPDATE of the row whose key value is repeated is executed. - renegator