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
0 answers

PHP MySQL Insert ... On Duplicate Key Update adding duplicate entry

I am pulling multiple arrays of information from a database and displaying items to the user when they load a page. If the user wants to create a new item they can and clicking a save button will send the information back to the database. If the…
Eric
  • 91
  • 2
  • 12
0
votes
0 answers

Pandas + MySQL On Duplicate Key Update Broken

My ON DUPLICATE UPDATE clause stopped updating and I'm not sure why. Below is my code to create a temporary table via Pandas: #connect to mysql database engine = sqlalchemy.create_engine('mysql://username:@localhost/db?charset=utf8') conn =…
Jason Melo Hall
  • 652
  • 2
  • 11
  • 23
0
votes
0 answers

MySql on duplicate key set last_insert_id is causing auto_increment to jump

I have a query that goes as follows: insert into TABLE(COLUMNS) values(VALUES) on duplicate key update id=last_insert_id(id) I use this to get an ID of inserted or existing row of data. $id=$connection->lastinsertid(); Well now I have a issue…
Maciek Semik
  • 1,872
  • 23
  • 43
0
votes
1 answer

Using "on duplicate key" with multiple duplicate condition

I have table contains questions data. Also every question has test id, document id (dokuman_id), order (sira) and video data. I want to create a query that insert new row if there isn't a row has same test id, document id and order; else just update…
eneskomur
  • 91
  • 1
  • 13
0
votes
1 answer

Distinguish duplicate key from updated key

I have a SQL query that creates an ID: insert into category(related,text) values(?,?) on duplicate key update id=last_insert_id(id) I then get my ID: $id=$connect->lastinsertid() Is there a way to distinguish between an ID that was found as a…
Maciek Semik
  • 1,872
  • 23
  • 43
0
votes
0 answers

PDO How to distinguish insert and duplicate/update lastinsertid

I am using a really awesome piece of code that inserts AND on duplicate key, it updates the duplicate id and gets the last insert id regardless of duplicate or not. Therefore I get the id of the last updated or the duplicate id. But is there a way…
Maciek Semik
  • 1,872
  • 23
  • 43
0
votes
1 answer

Duplicate Key Error in SQL and Delete and Update

I need to insert rows from select, if key is duplicate then delete entry and insert them Table1 ColumnA ColumnB ColumnC ColumnD A 1 A1 7/21/2017 B 2 B1 7/22/2017 C 3 C1…
TechJump
  • 79
  • 1
  • 12
0
votes
3 answers

insert into select on duplicate mysql query

I am trying to update on duplicate record in MySQL, I have a table with many column but i want to update only some column from another table with same desc as current table but it is not updating records. my query is: insert into…
VasaraBharat
  • 81
  • 15
0
votes
1 answer

Insert with on duplicate key update ignores index

So I've got a table product_supplier that I need to add data to from import_tbl. Product_supplier has three columns product_id, supplier_id and price. Import_tbl has the same columns plus some extra. What's most important and what I can't get…
Kobe Vervoort
  • 5
  • 3
0
votes
2 answers

Insert with on duplicate key update gives error 1064

I'm trying to use this query but whatever I do I cannot get it to work. I'm still very new to the on duplicate key update syntax, but I can't find anything wrong with it INSERT INTO product_leverancier (product_id, leverancier_id, prijs) SELECT…
Kobe Vervoort
  • 5
  • 3
0
votes
0 answers

Duplicate Key Value SQL Server

I have a table where I need to insert codes, years, and descriptions. When the table was created, the PKey was Year,Code I'm trying to insert the same code with a different year into the table So... Existing values Year | Code| Description 2016 …
Xenoranger
  • 421
  • 5
  • 22
0
votes
0 answers

How to use ON DUPLICATE KEY UPDATE?

Below is the table: item_id is PrimaryKey and AUTO INCREMENT, item_code is UNIQUE | item_id | item_code | item_name | ----------------------------------- | 1 A001 Apple | | 2 A002 Orange …
vbnewbie
  • 206
  • 6
  • 26
0
votes
1 answer

insert into... on duplicate update causes Unknown column in field list, but the field exists

I am getting the error "Error Code: Unknown column 't2.FSVisitsToDate' in 'field list' when I run my query but I cannot figure out where my query is wrong. Can anyone point out what I did wrong? INSERT INTO CMCustomer (CustomerNumber, …
Aaron Martin
  • 397
  • 2
  • 6
  • 17
0
votes
1 answer

MySQL ON DUPLICATE insert into another table

I have the following problem: Every author has a name and n-aliases for this name. Every alias comes from a source. Every alias has m sources. For example AUTHOR| ALIAS | SOURCE ------------------------- Will| Willy |George Will| Bill |…
Anh Tuan Nguyen
  • 971
  • 3
  • 13
  • 28
0
votes
0 answers

How do you properly construct an IF EXIST UPDATE ELSE INSERT?

I have a tables containing the dollars a customer spends as well as points they earn. I have a master table that I intend to accumulate the points/dollars and redistribute them to the stores so that points are shared accross all stores. The query…
Aaron Martin
  • 397
  • 2
  • 6
  • 17
1 2 3
…
20 21