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

bulk insert sqlalchemy core with on conflict update

I want to insert multiple items into a table and upsert the table on conflict. This is what I came up with the following from sqlalchemy.dialects.postgresql import insert meta = MetaData() jobs_table = Table('jobs', meta, autoload=True,…
Bazinga777
  • 5,140
  • 13
  • 53
  • 92
3
votes
2 answers

Why are there auto increment gaps in MySQL when violating a unique key constraint, but not a primary key constraint?

I understand that when using INSERT ... ON DUPLICATE KEY UPDATE in MySQL there are auto increment gaps when an insert fails. However -- I've noticed that the gaps only occur when a unique key constraint is violated. If the primary key constraint…
Kyle Chadha
  • 3,741
  • 2
  • 33
  • 42
3
votes
3 answers

Equivalent of ON DUPLICATE KEY UPDATE / UPSERT

I'm a PostgreSQL user that's new to SQL Server. I need to replicate ON DUPLICATE KEY UPDATE functionality (sometimes called UPSERT). I've got a table of users (simplified here) with age and sex. Using this sample query, but changing the id as…
Sir Robert
  • 4,686
  • 7
  • 41
  • 57
3
votes
1 answer

Differentiate between 'no rows were affected' and rows succesfully UPDATEd--to same value (MySQL and PHP)

I am executing SQL (MySQL) commands from PHP. There are several possible outcomes to each execution: Record updated to new value Record updated, but values happen to be the same Record finds no rows to update (ie, no rows match the WHERE clause) I…
Jo.P
  • 1,139
  • 4
  • 15
  • 35
3
votes
3 answers

PHP MySQL Insert Into & On Duplicate Key Problems

Is my code/syntax wrong? I'm not sure what is wrong and am new to this. I have created a table in PHPMyAdmin. It has two columns. One is "id" and is the primary key/auto-increment. The other column is "steamname". This code is supposed to take a…
Chatyak
  • 183
  • 1
  • 1
  • 11
3
votes
2 answers

SQL INSERT without duplicate (no primary or unique keys)

Let's consider that I have a Table A that I use to generate a Table B. I have no primary key or unique key I could use for a ON DUPLICATE KEY. My Table B has 3 columns: A, B, and C. I want to INSERT this only if the row already doesn't exist. I can…
Wistar
  • 3,770
  • 4
  • 45
  • 70
3
votes
1 answer

MariaDB / MYSql non-descript syntax error with update on duplicate key

I am getting a very non-descriptive error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (order_site_id, order_id, business_email,…
ehime
  • 8,025
  • 14
  • 51
  • 110
2
votes
5 answers

What is the correct syntax for INSERT INTO ... ON DUPLICATE KEY UPDATE in MySQL?

my table(s) structure (MySQL / each one is same as below) +-------+--------------+------+------+-------------------+ | Field | Type | Null | Key | Default | +-------+--------------+------+------+-------------------+ | id |…
Alper
  • 1,085
  • 3
  • 20
  • 39
2
votes
2 answers

Yes another ON DUPLICATE KEY UPDATE query

I've been reading all the questions on here but I still don't get it I have two identical tables of considerable size. I would like to update table packages_sorted with data from packages_sorted_temp without destroying the existing data on…
Andy Gee
  • 3,149
  • 2
  • 29
  • 44
2
votes
1 answer

Pandas: Split dataframe with duplicate values into dataframe with unique values

I have a dataframe in Pandas with duplicate values in Col1: Col1 a a b a a b What I want to do is to split this df into different df-s with unique Col1 values in…
Smbat
  • 71
  • 1
  • 8
2
votes
1 answer

on duplicate key update in snowflake

How can I use insert statement with on duplicate key update like MySQL in Snowflake? MySQL code : INSERT INTO table (column_list) VALUES (value_list) ON DUPLICATE KEY UPDATE c1 = v1, c2 = v2, ...; Since snowflake does not support ON…
Mukul Kumar
  • 564
  • 1
  • 5
  • 24
2
votes
1 answer

Duplicate key error when updating key field in a set of records

I have a master detail relation between two tables. Troq is the master table and Troq_Alma is the detail table. Troq_Alma primary key is formed by Troq primary key (Troq.cod) and "Num_Orden". This "Num_Orden" field is not only part of the PK of…
Atreide
  • 257
  • 1
  • 9
2
votes
1 answer

How to check how many rows were updated and inserted in a ON DUPLICATE statement?

I have a statement that looks like this: $count=0; while($row = pg_fetch_assoc($result)){ $sql=("INSERT INTO joblist (job_no, billed, completed, paid, paid_amount, inv_no, invoice, type, o_submitted, approval_date, gals, jobtype, name, state,…
Dimitri Bostrovich
  • 315
  • 1
  • 11
2
votes
1 answer

SQLAlchemy MySQL ON DUPLICATE KEY UPDATE

I am trying to use SQLALchemy MySQL ON_DUPLICATE_KEY_UPDATE() function, but it is not working as expected. from sqlalchemy.dialects.mysql.dml import Insert new_record={'id': 'val1', 'col1': 'new val'} # Here id is the primary key # my_table is a…
Mina
  • 21
  • 1
  • 2
2
votes
0 answers

Sequelize duplicate key value violates unique constraint

I am working on nodeJs, and postgresql. I am facing with an error duplicate key value violates unique constraint \"AuthoriseDates_pkey\" Key (id)=(371) already exists. I set the column id is autoIncrement: true, don't understand why this…
beginerdeveloper
  • 785
  • 4
  • 17
  • 43
1 2
3
…
20 21