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
0
votes
1 answer

Mysql ON DUPLICATE KEY error

I'm having a little trouble with duplicate keys. I'll start with my code. // Prepare to select all liabilities $stmt = $con->query("TRUNCATE TABLE `Students`"); $stmt = $con->prepare("SELECT student_no, legal_first_name, legal_surname, oen_number…
Nathan Pringle
  • 15
  • 2
0
votes
1 answer

Updating existing lines in MySql and treating Duplicated Keys

I have a MySql database containing data about users of an application. This application is in production already, however improvements are added every day. The last improvement I've made changed the way data is collected and inserted into the…
João Bruno Abou Hatem de Liz
  • 633
  • 10
  • 22
0
votes
1 answer

mysql insert and update on duplicate

I have table with several cols and I'm doing a multiplier insert to it. ie: INSERT INTO accounting_positions KEYS (`date`, `customer`, `type`, `product`, `volume`, `payout`, `count`) VALUES…
Tzook Bar Noy
  • 11,337
  • 14
  • 51
  • 82
0
votes
1 answer

Using conditional Update - On duplicate key update

I have the following table called 'RESULT' that I get values for, from another table called 'Temp_main' The fields in the 'RESULT' are like the following: StudentName | SujectName | Result --------------------------------- Adam | Math |…
Kumaran Senapathy
  • 1,233
  • 4
  • 18
  • 30
0
votes
2 answers

Update a specific field on key duplicate IF a certain condition is satisfied

I have a table to which I upload data from CSV files. The fields are like the following: StudentName | SubjectName| Result --------------------------------- Adam | Math | Fail Bob | History | Pass Catherine | Math |…
Kumaran Senapathy
  • 1,233
  • 4
  • 18
  • 30
0
votes
1 answer

Multiple Inserts with upon duplicate key update

What I am trying to achieve is similar to this example Singular Upon Key Insert However where mine differs is im trying to do multiple inserts at the same time. Im updating alot of rows. I would like the insert to increment the number of sessions…
Robbo_UK
  • 11,351
  • 25
  • 81
  • 117
0
votes
2 answers

Ignore duplicates?

Column1 1 1 2 3 4 4 4 5 Is there a way to query that column and only return 2 3 5 If I use Select Distinct it will return 1 2 3 4 5 How can I write a select statement that if there is a duplicity not to select just one but to…
user2140261
  • 7,855
  • 7
  • 32
  • 45
0
votes
1 answer

Can I combine INSERT, JOIN and ON DUPLICATE KEY UPDATE

Here's my database structure as it stands today... inventory_transactions store movements of inventory with quantity_offset value that is either negative or positivie. They also have an inventory_transaction_id shipments store shipments, which are…
Steven Sokulski
  • 354
  • 1
  • 4
  • 17
0
votes
1 answer

TRIGGER: Read NEW.values and OLD.values during "ON DUPLICATE KEY UPDATE"

I'm trying to write a mini-auditing system for individual tables in MySQL. I had good luck working with basic INSERT/UPDATE/DELETE commands, however now a table I'm interested in auditing uses INSERT ... ON DUPLICATE KEY UPDATE. By using a…
user645280
0
votes
1 answer

creating Trigrams using LinkedHashMap java

I am trying to create a trigram model using LinkedHashMap> where Entry is the entry of last inputed bigram (whose structure is: LinkedHashMap Now the problem is, being a map it does not store multiple keys (overwrites the existing key-value pair…
mag443
  • 191
  • 1
  • 4
  • 12
0
votes
1 answer

MySQL: update a record, on duplicate key leave NEWER one, and delete OLDER one? See inside

There is a table: CREATE TABLE `mytable` ( `user_id` INT(10) UNSIGNED NOT NULL, `thing_id` VARCHAR(100) NOT NULL DEFAULT '', `lock_date` DATETIME NOT NULL, `lock_id` VARCHAR(36) NOT NULL, PRIMARY KEY (`user_id`,`thing_id`) ) ENGINE=INNODB DEFAULT…
No Way
  • 183
  • 9
0
votes
2 answers

How to make MySQL return conflicting key on duplicate key? See inside

Say, I've got a simple table: id as primary key, and field. Then i do: INSERT INTO table(id,field) VALUES (1,'blah') and then I do it again. So, how can I make MySQL return following: id 1 So that it works like SELECT-ing conflicted key?
No Way
  • 183
  • 9
0
votes
0 answers

Use ON DUPLICATE KEY UPDATE for some part of unique mapping key

I have table as shown below ItemAttMapID ItemAttributeID VendorID vItemID Qty 1 4 1 2 60 2 4 2 3 10 3 3 …
Pratik
  • 1,531
  • 3
  • 25
  • 57
0
votes
2 answers

MS Access: how to achieve ON DUPLICATE KEY UPDATE?

Trying to avoid separate queries to check for key existance and then inserting or updating accordingly. From Googling around it seems that Access doesn't support SQL server's MERGE either. Thanks
Richard H
  • 38,037
  • 37
  • 111
  • 138
0
votes
1 answer

ON DUPLICATE KEY causing timeout

I am currently running the following SQL statement on my MySQL database: INSERT INTO `Table` (`Col1`, `Col2`, `Col3`) VALUES ('a','b','c') ON DUPLICATE KEY UPDATE `Col1`=`Col1`, `Col2`=`Col2`, `Col3`=`Col3` However, everytime I do run it, I get the…
matt
  • 2,857
  • 7
  • 33
  • 58
1 2 3
…
20 21