Stack Overflow
Stack Overflow

Questions tagged [on-duplicate-key]

ON DUPLICATE KEY UPDATE is an SQL feature in MySQL which allows a row to be updated instead on inserted in an INSERT query, if it would otherwise attempt to create a duplicate key in a unique index.

ON DUPLICATE KEY UPDATE is an SQL feature in MySQL which allows a row to be updated instead on inserted in an INSERT query, if it would otherwise attempt to create a duplicate key in a unique index.

Example:

INSERT INTO t (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

So, if c is a unique column in table t, and the key 3 already exists for column c, then MySQL executes the update c=c+1 rather than inserting.

This feature eliminates the need for an extra query check for duplicate key values.

Reference: MySQL Manual

305 questions
7
votes
5 answers

Prevent InnoDB auto increment ON DUPLICATE KEY

I am currently having problems with a primary key ID which is set to auto increment. It keeps incrementing ON DUPLICATE KEY. For Example: ID | field1 | field2 1 | user | value 5 | secondUser | value 86 | thirdUser | value From the…
0x0
  • 363
  • 1
  • 4
  • 19
7
votes
3 answers

Django database migration error: duplicate key

I'm working on migrating a database and I'm not sure why I'm getting this error. Does anyone know how to fix this? Before this, I created a new database using mysql and granted access to users. I had a working database before but the application…
AceGravity
  • 333
  • 2
  • 6
  • 13
7
votes
3 answers

timestamp autoupdate not working with ON DUPLICATE KEY UPDATE (PDO)

I've a table folio with timestamp set to auto update. CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PDO statement in PHP is not causing the timestamp to update. $statement = $this->connection->prepare(" INSERT INTO folio(publication, productId)…
user2727195
  • 7,122
  • 17
  • 70
  • 118
6
votes
2 answers

Prepared Statement syntax for ON DUPLICATE KEY UPDATE (number of params error)

Here is what I am trying to do. I want to insert into this table or update the record if the primary key(entity_id) exists. I am just having an issue with the SQL syntax. It wont let me have more params than the first amount of 'VALUES' so I get…
KisnardOnline
  • 653
  • 4
  • 16
  • 42
6
votes
2 answers

Conditional ON DUPLICATE KEY UPDATE

I've done some research but nothing seems to fit my needs. I have a database table that contain some data retrived from a webservice. A user perform some tasks for each record, and then flag it as "processed". So i have an additional db field (not…
Luciano
  • 1,455
  • 8
  • 22
  • 52
5
votes
4 answers

How to add prefix/suffix on a repeatable dictionary key in Python

Could you please suggest is there any way to keep all the repeatable (duplicate) keys by adding prefix or suffix. In the below example, the address key is duplicated 3 times. It may vary (1 to 3 times). I want to get the output as in the expected…
Sri
  • 85
  • 4
5
votes
1 answer

How to insert bulk rows and ignore duplicates in postgresql 9.3

I have many rows of data to be inserted into a table. Table already has data in it. When I do a bulk insert like this, INSERT INTO permission(username, permission) values(('john','ticket_view'), ('john','ticket_modify'),…
Velu narasimman
  • 543
  • 1
  • 5
  • 18
5
votes
3 answers

MySQL INSERT on duplicate key UPDATE with SELECT

I have a query like the following INSERT INTO connections (`c_id`,`in`,`out`,`ip`,`userID`) VALUES ( ( SELECT c_id FROM connections WHERE (a bunch of conditions) ORDER BY c_id DESC LIMIT 1 ), …
php_nub_qq
  • 15,199
  • 21
  • 74
  • 144
4
votes
1 answer

Shift a whole range of keys

I am working on time series data, for which the key column is a timestamp : Time. There are also many "value" columns for each row. I am about to shift a whole range of my data by several hours (due to a daylight saving time issue). For that, I…
Raphael Jolivet
  • 3,940
  • 5
  • 36
  • 56
4
votes
2 answers

ON DUPLICATE KEY UPDATE - precedence

When doing an INSERT INTO {tableA} SELECT a,b,c FROM {tableB} ON DUPLICATE KEY UPDATE x=y What is the precedence on how the duplicate keys are evaluated? I assume that MySQL first checks to see if a tuple from tableB exists clashes with a…
Jesse Skrivseth
  • 481
  • 2
  • 13
4
votes
4 answers

JSON fields have the same name

In practice, keys have to be unique within a JSON object (e.g. Does JSON syntax allow duplicate keys in an object?). However, suppose I have a file with the following contents: { "a" : "1", "b" : "2", "a" : "3" } Is there a simple way…
econ
  • 547
  • 7
  • 22
4
votes
2 answers

Why mysql INSERT ... ON DUPLICATE KEY UPDATE can break RBR replication on a master / master configuration

here is the problem: 2 MySQL 5.5 servers Row based replications + master master Writes are on both servers (both active) autoinc trick (1 server odd, the other one even) I have a table like byUserDailyStatistics: id (PK + AUTO INC) date…
nemenems
  • 1,064
  • 2
  • 9
  • 27
4
votes
2 answers

Laravel FirstOrCreate and Save() duplicate entry

I have a table like this Schema::create('user', function(Blueprint $table) { $table->increments('id'); $table->datetime('DateTimeCode'); $table->integer('user_id'); $table->integer('Foo'); …
Wistar
  • 3,770
  • 4
  • 45
  • 70
4
votes
4 answers

MySQL "Insert ... On Duplicate Key" with more than one unique key

I've been reading up on how to use MySQL insert on duplicate key to see if it will allow me to avoid Selecting a row, checking if it exists, and then either inserting or updating. As I've read the documentation however, there is one area that…
phouse512
  • 650
  • 4
  • 15
  • 27
3
votes
1 answer

INSERT INTO … ON DUPLICATE KEY UPDATE …. with condition?

I am still wondering if there is something like a conditional on duplicate update in MySQL 5.7 I have a table which is updated by different sources. Let’s assume I have a table CREATE TABLE t ( name VARCHAR(100), value INT, last update…
Thallius
  • 2,482
  • 2
  • 18
  • 36
1
2
3
…
20 21