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
200
votes
9 answers

On Duplicate Key Update same as insert

I've searched around but didn't find if it's possible. I've this MySQL query: INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8) Field id has a "unique index", so there can't be two of them. Now if the same id is already present in the…
Roy
  • 4,254
  • 5
  • 28
  • 39
93
votes
5 answers

Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

I know that you can use ON DUPLICATE KEY UPDATE to update a certain value if there is a record for that key already, I can do this: INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `a`=1, `b`=2, `c`=3 But how can I do…
Naftali
  • 144,921
  • 39
  • 244
  • 303
77
votes
2 answers

PHP MYSQL UPDATE if Exist or INSERT if not?

I have no idea if this is even remotely correct. I have a class where I would like to update the database if the fields currently exist or insert if they do not. The complication is that I am doing a joining 3 tables (set_colors, school_art,…
GGcupie
  • 1,003
  • 1
  • 8
  • 11
55
votes
3 answers

MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

For loading huge amounts of data into MySQL, LOAD DATA INFILE is by far the fastest option. Unfortunately, while this can be used in a way INSERT IGNORE or REPLACE works, ON DUPLICATE KEY UPDATE is not currently supported. However, ON DUPLICATE KEY…
Jan
  • 3,044
  • 3
  • 20
  • 32
21
votes
9 answers

Doctrine: ON DUPLICATE KEY UPDATE

How can I write an INSERT doctrine query with option ON DUPLICATE KEY UPDATE?
Leonardo
  • 2,273
  • 6
  • 29
  • 32
20
votes
2 answers

PDO prepared statements for INSERT and ON DUPLICATE KEY UPDATE with named placeholders

I'd like to switch PDO INSERT and UPDATE prepared statements to INSERT and ON DUPLICATE KEY UPDATE since I think it'll be a lot more efficient than what I'm currently doing, but I'm having trouble figuring out the correct syntax to use with named…
Chaya Cooper
  • 2,566
  • 2
  • 38
  • 67
17
votes
2 answers

Too many auto increments with ON DUPLICATE KEY UPDATE

I have a basic table with columns: id (primary with AI) name (unique) etc If the unique column doesn't exist, INSERT the row, otherwise UPDATE the row.... INSERT INTO pages (name, etc) VALUES 'bob', 'randomness' ON DUPLICATE KEY UPDATE name =…
Nathan Waters
  • 1,173
  • 4
  • 13
  • 23
16
votes
2 answers

EF6 CodeFirst My [Key] Id Column is not auto-incrementing as an identity column should

I have several classes that I need to derive from a common base class which holds an Id. Ignoring the all but one of these for the moment, let's say we have: public class MyBase { [Key] public int Id { get; set; } } public class MyName :…
Steve L
  • 1,523
  • 3
  • 17
  • 24
11
votes
1 answer

insert select from on duplicate key ignore postgres

I've read from this source that you can do an insert on dupcliate key ignore in postgres, but I cannot seem to get this to work for a select from: link What I've seen you can do is: insert into tab(id, val) values(1, 'nosert'), (2, 'nosert'), (3,…
Alexander Kleinhans
  • 5,950
  • 10
  • 55
  • 111
10
votes
2 answers

SQL Server: multiple INSERT with MERGE

What is the method to do multi insert of values into SQL Server database? Usually in MySQL I use queries like: INSERT INTO table (column1, column2) VALUES(value1, value2), (value3, value4) ON DUPLICATE KEY UPDATE column2 = VALUES(value2); Is there…
Petr Hubík
  • 191
  • 1
  • 1
  • 9
8
votes
7 answers

Yii INSERT ... ON DUPLICATE UPDATE

I am working on a Yii project. How can I use the ON DUPLICATE feature of MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ) when doing a save() on a Yii model? My MySQL is as follows: CREATE TABLE `ck_space_calendar_cache` ( …
Nathan H
  • 48,033
  • 60
  • 165
  • 247
8
votes
2 answers

return id in ON DUPLICATE KEY in JOOQ

What I actually want is to write following query in JOOQ: stmt = connection.prepareStatement( "INSERT INTO `tbl` (`name`, `service_id`, `device_id`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)", …
Majid Azimi
  • 5,575
  • 13
  • 64
  • 113
8
votes
4 answers

Getting count of insert/update rows from ON DUPLICATE KEY UPDATE

I have a statement that tries to insert a record and if it already exists, it simply updates the record. INSERT INTO temptable (col1,col2,col3) VALUES (1,2,3) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3); The full…
Shaun Perry
  • 159
  • 1
  • 2
  • 12
8
votes
2 answers

update on duplicate key update

I have a table playerspoints that contains a shop id and a player's id, and a player's points. SHOP_ID | PLAYER_ID | POINTS ---------------------------------------- 1 | 7 | 66 2 | 4 …
Not Amused
  • 942
  • 2
  • 10
  • 28
8
votes
2 answers

MySql - ON DUPLICATE KEY INSERT

I understand that there exists INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE. However, when there is a duplicate key, I'd like to do a INSERT to a temporary table to keep a record of the unique key that has been violated, so that I can output…
matt
  • 2,857
  • 7
  • 33
  • 58
1
2 3
…
20 21