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

Case expression not working as expected with 'on duplicate key update' clause

Through the following query, I want to update the ConcurrentJobs column only when the value in MaxConcurrentJobs column is greater than or equal to the value of ConcurrentJobs being updated. insert into userjobinfo (UserId,ConcurrentJobs) values…
Suhail Gupta
  • 22,386
  • 64
  • 200
  • 328
0
votes
1 answer

Sql Query with key value based on date and user?

I'm trying to post values into db but this is getting a bit to tricky for me. Since there gonna be multiple posts with "username" each date I can't make any of these a key value. So I was thinking, if i put a value called "kay" that throws date…
Petrus Alli
  • 15
  • 5
0
votes
1 answer

replacing Duplicate rows in a table and in tables having foreign key reference to the 1st table

I have 2 tables table 1: teams with column Teamid ,teamName which has some duplicates like Table 1 TeamID | TeamName ------------------------------------- 1 | abc 2 | abc table 2 :UsedTeams with…
Ramveer Singh
  • 39
  • 1
  • 5
0
votes
1 answer

on duplicate key update in laravel 5.0 eloquent

I have a table "app_sessions" in my database with two unique keys - device_id and device_token. I want to insert a new session in this table only if the record with device_id or device_token doesn't exist, otherwise just update the session for this…
janardhan
  • 3
  • 3
0
votes
2 answers

Conditional on duplicate key update not working correctly

I need to update my created_on datetime field value to NOW() only if the old value for is_active field was 0 and it changed to 1. +-----------+---------+-----------+---------------------+---------------------+ | device_id | user_id | is_active |…
Shoaib Ahmed
  • 424
  • 2
  • 9
0
votes
0 answers

Mysql does'nt distinguish between characters "c" and "ç" in UNIQUE index

I have simple table with 2 fields: first: `id` INTEGER primary key auto-incremented second: `symbols` VARCHAR unique key 3-length utf8_general_ci. Lets say there is just one row already (comma separates fields): 1, c now I want to add some…
ElSajko
  • 1,612
  • 3
  • 17
  • 37
0
votes
0 answers

after INSERT ... ON DUPLICATE UPDATE, I got 0 from mysqli .insert_id

After I ran the INSERT ... ON DUPLICATE UPDATE query with PHP, I used .insert_id to find out the newly inserted row or founded row to do another insert onto another table. But sometimes, .insert_id would return 0 for no updates. I still need to…
xam
  • 452
  • 2
  • 7
  • 15
0
votes
1 answer

php - update sql statement with multiples rows

I know this problem has already been around but I cannot figure out how to solve it in my case. I try to use the UPDATE statement with sql but I have a problem when dealing with multiples rows. This is my code: $body =…
Trichophyton
  • 625
  • 5
  • 21
0
votes
0 answers

ON DUPLICATE KEY UPDATE is not working for multiple column in mysql 5.1

I have a table test with following structure: CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `lat` varchar(100) NOT NULL, `long` varchar(100) NOT NULL, `updatedon`…
Faiyaz Alam
  • 1,191
  • 9
  • 27
0
votes
1 answer

ON Duplicate Key for exact combination of multiple values

Let's assume we have this table: -------------------------- | x | y | z | data | --------------------------- | 3 | 53 | 24 | hello | --------------------------- - Now I only want to update "data" in case there is the exact combination of…
Bob
  • 1,201
  • 1
  • 9
  • 22
0
votes
1 answer

On duplicate key update involving table to table insert

I used the following script to populate a table. This script worked just fine: insert into TSC_Intermediate.dbo.stock (article , description , article_id , client ,…
philm
  • 1
  • 1
  • 2
0
votes
2 answers

How to avoid duplicate inserts for an OrientDB database?

In SQL there's a query INSERT IGNORE which keeps duplicate entries out of the database based on the primary key. But is there a way to achieve this functionality in OrientDB since the primary key concept here is kind of achieved using the @rid…
Rajith Gun Hewage
  • 532
  • 7
  • 25
0
votes
1 answer

MySQL - on duplicate key - CASE WHEN THEN ELSE doesnt work

I want to insert an entry (uid, A, B) to database. If the entry is existed already, I will update the 'A' and 'B' column with condition: (1 = old entry; [2] = new entry) A = (B[1] == B[2]) ? A[1] : A[2]; B = B[2] + 1; My query: INSERT INTO…
Anh-Tuan Mai
  • 1,129
  • 19
  • 36
0
votes
1 answer

SQL INSERT ... ON DUPLICATE KEY - return number of only newly inserted rows, not number of newly inserted + updated rows?

How can I get this? I know I can do this by getting COUNT of all rows before running this Insert query, and then substract it from COUNT of all rows after running this Insert query, but it's not practical for big databases. I also found this formula…
kecman
  • 813
  • 3
  • 14
  • 34
0
votes
4 answers

How to sum the values of column on duplicate key?

I have a table like this: // mytable +----+--------+-------+-------+ | id | name | key | value | +----+--------+-------+-------+ | 1 | jack | 1 | 10 | | 2 | peter | 1 | 5 | | 3 | jack | 2 | 5 | | 4 | ali | 1 …
Shafizadeh
  • 9,960
  • 12
  • 52
  • 89
1 2 3
…
20 21