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

insert, if exist update using trigger

I want to implement a Viewed system for my website. Here is the structure of my tables: // table1 id | user_or_ip | post_id | date_time // inserting new row for each viewed // table2 id | post_id | total_viewed // getting the number…
stack
  • 10,280
  • 19
  • 65
  • 117
0
votes
1 answer

mysql insert update on duplicate key - strange behaviour when col has default null

I have a table my_table: col_a varchar(20) not null, no default col_b varchar(10) not null, no default col c varchar(5) null, default null there is a unique index on col_a, col_b there is an existing row where: col_a = 'ABCDEF-123' and col_b =…
user5568662
  • 25
  • 5
0
votes
1 answer

python sql statement optimalization in python

I found the only way how to update only null variables in mysql db with python. I have this kind of statement: sql = "INSERT INTO `table` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)\ ON DUPLICATE KEY UPDATE…
Matej Petráš
  • 67
  • 11
0
votes
1 answer

MySQL: INSERT ON DUPLICATE KEY UPDATE not all the fields

I have a users table as below: id --- name --- email --- gender id column is both primary key and unique key. Here I'd like to update the rows with new name and email information but I don't have to change their gender. I tried to update table…
Patrick
  • 2,889
  • 1
  • 24
  • 24
0
votes
3 answers

INSERT INTO SELECT CROSS JOIN Composite Primary Key

I'm performing an INSERT INTO SELECT statement in SQL Server. The situation is that there are two Primary keys of two different tables, without anything in common, that are both foreign keys of a third table, forming a composite primary key in that…
Dean Zaslow
  • 87
  • 2
  • 9
0
votes
2 answers

MySQL On Duplicate Insert

I'm new to MySQL so I can't figure out whats wrong in the syntax. $sql = "INSERT INTO UsersTest (user_ip, email, firstname, lastname, city, state, country) VALUES ('$user_ip', '$email', '$firstname', '$lastname', '$city', '$state', '$country') ON…
eozzy
  • 66,048
  • 104
  • 272
  • 428
0
votes
2 answers

Using INSERT … ON DUPLICATE KEY UPDATE with several keys

I have a DB in which I would like one of three keys (userId, udid and token) to identify the row: all three of them are unique keys and the first is also primary and autoincremental. Basically I would like to update all the values when one of the…
Fabrizio Bartolomucci
  • 4,948
  • 8
  • 43
  • 75
0
votes
1 answer

Duplicate key update MySQL not updating

I have two Unique keys in one table. I'm inserting the data from an csv file. Unique keys are: enrollmentNo and subjectCode My query is: Insert into result_stud_det(enrollmentNo,departmentCode,subjectCode,semester,marks,enrSubjCode) values…
Manoj Kumar Giri
  • 49
  • 7
0
votes
1 answer

Sql update: continue updating after duplicate key error (transact-SQL)

I have the following script: update myTable set FieldValue = 1 where FieldValue = 2 Now lets assume that I have 10 entries in mytable and when updating the 5th element, I get the duplicate key insertion error: Cannot insert duplicate key row in…
Szabolcs Antal
  • 877
  • 4
  • 15
  • 27
0
votes
2 answers

Update Multiple Rows mysql

I have a table tbl |id|points| 1 15 2 35 3 445 4 42 Now if i have an array like array (2=>10,3=>825,4=>48) And i want to change the points so that the tbl looks like this. |id|points| 1 15 2 10 3 825 4 48 I can change the…
Slim Shady
  • 1,045
  • 2
  • 12
  • 38
0
votes
1 answer

Delete duplicated rows from my database + Make sure no more duplications will be added

I know alot of people asked this question before but I didn't find an absloute answer and I saw alot of different answers so i'm confused. I'v got the following data for example in my sql database: user: test code: blablah email:…
BenTyler
  • 43
  • 1
  • 9
0
votes
1 answer

Can Sequel be forced to always return autoincrement id with ON DUPLICATE KEY UPDATE?

I'm working with Sequel (4.x) and MySQL 5.5, on a write-heavy application performing mostly INSERTs and occasionally updating existing rows. Sequel returns an AUTO_INCREMENT id following an .insert() action. I'm currently using…
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
0
votes
0 answers

Insert Into.. Select.. On Duplicate Key Update

I'm trying to test how the insert into duplicate key works. And currently this is what I did: INSERT INTO user_test (userName, first, last) SELECT u.userName, u.first, u.last FROM otherdatabase.user as u ORDER BY u.userName ASC ON DUPLICATE KEY…
hocuspocus31
  • 183
  • 1
  • 10
0
votes
2 answers

'insert into ... on duplicate key' with auto increment

I am trying to do an INSERT only if the combination of two columns (a and b) does not exist already. Otherwise, I want to do an UPDATE. The issue of the following code is that it always INSERTs a new row instead of updating when I want to. The…
fazac
  • 25
  • 4
0
votes
1 answer

Why is affectedRows takes time to update

MySQL documentation states: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. However, while using it, I get a different result. For a same multiple…
Finstairn
  • 93
  • 1
  • 6
1 2 3
…
20 21