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

Flex / AIR local SQL support for ON DUPLICATE KEY UPDATE?

As the title says does the SQL implementation in Adobe's AIR platform support "ON DUPLICATE KEY UPDATE"? I am unable to get it to work but it is possible I am using wrong syntax especially if it's different from MySQL syntax. Documentation is very…
DominicM
  • 6,520
  • 13
  • 39
  • 60
0
votes
2 answers

Using INSERT INTO On Duplicate Key UPDATE for form input

How do you use INSERT INTO On Duplicate Key UPDATE for form input? All the examples I've found online are with counters or predetermined values. I've been able to get my code to work (thanks to some really helpful members) with the standard…
Chaya Cooper
  • 2,566
  • 2
  • 38
  • 67
0
votes
1 answer

insert into select using different column in duplicate key update

INSERT INTO options (owner, name, value, modified) SELECT owner, name, value, modified, @draft:=draft FROM ( ... ) `options` ON DUPLICATE KEY UPDATE value=VALUES(value), modified=@draft Above will error with column count doesn't match row…
Alex
  • 2,102
  • 1
  • 13
  • 16
0
votes
1 answer

How to use ON DUPLICATE KEY for multiple entries?

In a single entry, we can use ON DUPLICATE KEY to UPDATE the value: INSERT INTO table (title, number) VALUES ('$title', '$amount') ON DUPLICATE KEY UPDATE number=number+$amount How to use `ON DUPLICATE KEY for multiple entries as INSERT INTO…
Googlebot
  • 15,159
  • 44
  • 133
  • 229
0
votes
1 answer

Update MySQL row containing retweet_count if it is retweeted

I've been logging tweets into a MySQL database using the Twitter API and PHP using the following function: function saveTweets($screen_name) { global $link; $screen_name = dbEscape(strtolower(trim($screen_name))); if (!$screen_name) { echo…
Phil
  • 103
  • 7
0
votes
2 answers

Adding a date to mysql value being inserted if value exists in column

I have a MySQL table like that is structured like this: | uri | title | _________________ _____________ Latest-Update Latest Update Latest Update where the uri column is an INDEX. I have occassional…
Tower
  • 1,287
  • 3
  • 15
  • 25
-1
votes
2 answers

ON DUPLICATE KEY ALTER TABLE

I am trying to run the following mysql query, I am not getting any errors , but its also not working either?. I just want it to update the column name if a duplicate is detected. thanks :o)
Iain Simpson
  • 441
  • 4
  • 13
  • 29
-1
votes
2 answers

how do i increment a value of column with condition?

my table looks something like this id effect1(int) effect2(int) effect3(int) 1 0 1 5 2 1 0 0 i have a function, which return 1to3 int value representing effect1,effect2,effect3 and "id" i am…
mohammad eunus
  • 51
  • 8
-1
votes
1 answer

Insert on duplicate update

I have MySQL table and I need to make query INSERT ON DUPLICATE UPDATE. "id" is primary key, I dont need it for now. I need to insert values "id2", "name", "city" into the table. If there is some row with the same values in "id2" and "name", then…
Patrik
  • 1,269
  • 7
  • 30
  • 49
-1
votes
2 answers

Updating a table with a composite key not working correctly

I have the following table: As you can see, date and bed form a composite primary key. I am trying to run this query: INSERT INTO days (date, operating_time, bed) VALUES ('2016-11-07', 6.55, 1) ON duplicate key update…
Matthew Goulart
  • 2,873
  • 4
  • 28
  • 63
-1
votes
1 answer

MYSQL - ON DUPLICATE KEY does not update

I having been having the worst luck with updating and inserting into a database. My insert into a database works perfectly, i just cannot update. I have tired two seperate queries one for update the other insert, but updating never works. I am now…
jerneva
  • 473
  • 1
  • 8
  • 25
-1
votes
1 answer

INSERT SELECT * FROM ON DUPLICATE Results in Error 1136

I am attempting to insert/update a df into a mysql table. I'm receiving this error "_mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")" on the execute line in the code below. Any suggestions on a…
Jason Melo Hall
  • 652
  • 2
  • 11
  • 23
-1
votes
1 answer

ON DUPLICATE KEY UPDATE keeping some sold values

ive googled for 1½ hour and cant come to an understanding how to do this. i want to update my current row, but keep values that are already assigned. i currently have a checkbox which either gives "1" or "0". if the checkbox returns "0" i dont want…
saknar namn
  • 139
  • 1
  • 1
  • 10
-1
votes
1 answer

insert into..on duplicate key with different column names

I have two tables profieltest and parametertest. I want the value column in profieltest to be updated with the parameters from parametertest where the user id is the same and where the field_id = 2 in profieltest . However if the combination…
Florian
  • 725
  • 6
  • 27
-2
votes
1 answer

mysql version 5.7.39 query problem condition with on duplicate

hi everyone i need help create table wishlist ( userID int null, wishlistData longtext collate utf8mb4_bin not null, constraint userID unique (userID), constraint whishlist_user_id_fk …
med
  • 1
  • 1
1 2 3
…
20
21