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

#1022 Duplicate foreign key - MySQL bug

I have problem with add FOREIGN KEY to table catalog_product_flat_8 - Magento 1.9 When I try reindexing, I get: Product Flat Data index process unknown error: Exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation:…
lukas_osw
  • 29
  • 1
2
votes
1 answer

MySQL ON DUPLICATE KEY UPDATE and CONCAT

I have a query (Code is the PRIMARY KEY): INSERT INTO table (Code, ... events) VALUES (1, ... CONCAT(events, 'BAR')), (2, ... CONCAT(events, 'BAR')), ... ON DUPLICATE KEY UPDATE ... events = VALUES(events) My intention is that…
Pavel Zorin
  • 331
  • 6
  • 17
2
votes
2 answers

H2 INSERT SELECT ON DUPLICATE KEY UPDATE throws "Unique index or primary key violation" error

H2 (started with MODE=MYSQL on) supports INSERT ON DUPLICATE KEY UPDATE statement only with VALUES clause, while throws a "Unique index or primary key violation" error when using INSERT SELECT statement. Here is an example: -- creating a simple…
user1781028
  • 1,478
  • 4
  • 22
  • 45
2
votes
1 answer

MYSQL Bulk insert if not exists update if exists

I am looking for something similar- insert into table1(a, b, c) select col1 as d, col2 as e, col3 as f from table2 on duplicate key update b = e, c = f; NOTE - Here table1.a is unique Key. I am getting error- Unknown column name 'e'. Is there any…
Abhi
  • 1,963
  • 7
  • 27
  • 33
2
votes
1 answer

MySQL — ON DUPLICATE KEY UPDATE only when both unique fields match, else INSERT

I have the following query: "INSERT INTO `occ_apps` (`occ_date`, `service_tag`, `counter`) VALUES (?, ?, '1') ON DUPLICATE KEY UPDATE `counter` = (`counter`+1)" Currently it's incrementing the counter when either occ_date or service_tag is…
Mark Twain
  • 199
  • 1
  • 2
  • 10
2
votes
1 answer

Conditional onDuplicateKeyUpdate in Jooq

I tried to use the jooq dsl for an insert/update query. We have a unique (MemberId, GroupId) combination in our table, and a group membership Enum. On sending a subscription request with (MemberId, GroupId, RoleEnum) The query should insert a new…
Yossi Chen
  • 226
  • 3
  • 11
2
votes
2 answers

ON DUPLICATE KEY unrecognized in phpmyadmin

I have a mysql database hosted on a remote server, I'm using InnoDB. Everything else works fine but on phpmyadmin whenever I try to execute the following query INSERT INTO User (user_id, location) VALUES (1, 'London') ON DUPLICATE KEY UPDATE…
Poka Yoke
  • 373
  • 3
  • 8
  • 27
2
votes
0 answers

Insert into table on duplicate update but keep certain columns old values

I have a myIsam table people as follow: [id][name][cat] with values: 12,"John Doe", "worker" id is unique and name is fulltext. I put some values inside by a remote file call. Then I change manually cat value to another different…
Angel C.
  • 21
  • 3
2
votes
3 answers

PHP MySQL - INSERT and ON DUPLIATE KEY UPDATE with WHERE?

I want to be able for members to add more info (location, story) to their profile and also update their password if needed. For that I use the following snippet: $query = mysql_query(" INSERT INTO members (location, story) VALUES…
Anja
  • 21
  • 2
2
votes
1 answer

Bulk insert and update in mysql

I am trying to make some bulk insert in my table but it should cater for duplicate values. If there are duplicate then it should update the rows instead of updating it. My table is: user(id, name, url) where id is PK I tried this: INSERT INTO…
user2707590
  • 1,066
  • 1
  • 14
  • 28
2
votes
2 answers

ON DUPLICATE KEY UPDATE - with condition

Haven't worked a lot with INSERT INTO ... ON DUPLICATE KEY UPDATE queries yet, so please lead me to a solution. The database table is named 'tb_logs' with 4 columns: log_user_id, log_visitor_id, log_date, log_counter I want to insert data, and only…
lickmycode
  • 2,069
  • 2
  • 19
  • 20
2
votes
3 answers

Using JFactory::getDbo()->insertObject with on duplicate key update

How to use: JFactory::getDbo()->insertObject('#__card_bonus', $object); with on duplicate key update ?
Rudolf Pipopulo
  • 110
  • 1
  • 10
2
votes
1 answer

INSERT from SELECT with ON DUPLICATE KEY UPDATE

I am trying to run an INSERT query, based upon a SELECT, with an "ON DUPLICATE KEY UPDATE" statement. The SELECT query works, and the resulting data if I were to input it "manually" will result in a duplicate key issue. So far so good. However, the…
Tjeerd Kramer
  • 223
  • 1
  • 2
  • 12
2
votes
1 answer

insert update multiple rows mysql

I need to add multiple records to a mysql database. I tried with multiple queries and its working fine, but not efficient. So I tried it with just one query like below, INSERT INTO data (block, length, width, rows) VALUES ("BlockA", "200", "10",…
Irawana
  • 269
  • 3
  • 6
  • 14
2
votes
1 answer

Hibernate - Autofill record ID on duplicate key

I am loading list of tenders from text files (one tender one file) source and I would like to store it into database. I have tables address_point, street, city and country where address_point is refered from many tables (organization, tenders,…
radeklos
  • 2,168
  • 21
  • 19
1 2 3
…
20 21