On Duplicate Key Update same as insert

Question

I've searched around but didn't find if it's possible.

I've this MySQL query:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)

Field id has a "unique index", so there can't be two of them. Now if the same id is already present in the database, I'd like to update it. But do I really have to specify all these field again, like:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY UPDATE a=2,b=3,c=4,d=5,e=6,f=7,g=8

Or:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g)

I've specified everything already in the insert...

A extra note, I'd like to use the work around to get the ID to!

id=LAST_INSERT_ID(id)

I hope somebody can tell me what the most efficient way is.

AFAIK, the method of restating each column, `a=VALUES(a), b=VALUES(b), ...` is the way you need to go. That's how I do it for all of my `INSERT ON DUPLICATE KEY UPDATE` statements. — Valdogg21, Jan 17 '13 at 16:24
Just to clarify, everything stated here is correct as long as you understand the question being answered is: Must you restate every column you WANT TO UPDATE? Yes, if you want to insert or update 8 columns in a 25-column table, you must state the 8 columns twice -- once in the insert part and once in the update part. (You could skip the primary key in the update part, but it's easiest to preformat an "a=1,b=2,c=3" string and embed it twice.) However, you do NOT have to restate the remaining 17 columns you don't want to change. If it becomes an UPDATE, they'll keep their existing values. — Timberline, Jul 11 '13 at 22:27
I added that comment because the questions and answers left me unsure about unmentioned columns with this kind of INSERT, which I then tested after learning exactly what to test. INSERT ON DUPLICATE KEY UPDATE leaves unmentioned columns unchanged on UPDATE but gives them default values on INSERT. With REPLACE INTO, unmentioned columns always get default values, never existing values. — Timberline, Jul 11 '13 at 22:32
Check http://stackoverflow.com/questions/2472229/insert-into-select-from-on-duplicate-key-update, I think there is what you're looking for — Federico J., Nov 25 '13 at 10:19

score 104 · Answer 1 · answered Jan 17 '13 at 16:26

104

The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?

For eg. if your columns a to g are already set as 2 to 8; there would be no need to re-update it.

Alternatively, you can use:

INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY
    UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

To get the id from LAST_INSERT_ID; you need to specify the backend app you're using for the same.

For LuaSQL, a conn:getlastautoid() fetches the value.

answered Jan 17 '13 at 16:26

hjpotter92

78,589
36
144
183

7

It's just for the example. In a real life query there are differences. My question is quiet simple: Do I really need to specify all the fields (and values in my first example) if they are the same as in the insert? I just want to insert all or if there is a unique value match: update all. – Roy Jan 17 '13 at 16:36
1

"A extra note, I'd like to use the work around to get the ID too!" – Agamemnus Oct 17 '14 at 16:39
What is the difference between `UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;` and `UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g);` ? – Lewis Apr 24 '15 at 06:28
1

@Tresdin, As far as I can tell it is just syntactic sugar. Personally, I have never seen anybody using the a=VALUES(a), b=VALUES(b), etc. Maybe you can assign multiple values to the column? Not sure why you wouldn't just concatenate the data beforehand though. – RattleyCooper May 06 '15 at 21:38
75

If table `tbl` already has row (1,10) then: `INSERT INTO tbl(id, a) VALUES(1,2) ON DUPLICATE KEY UPDATE a=VALUES(a)` will set a = **2**. While `INSERT INTO tbl(id, a) VALUES(1,2) ON DUPLICATE KEY UPDATE a=a` will set a = **10** – Bùi Việt Thành Jul 23 '15 at 10:07
11

Why all the upvotes? This DOES NOT WORK. As @Bùi Việt Thành pointed out, using `a=a` updates nothing. (The queries in the original question don't actually update anything either, but an update seems to be what the OP intended.) – Roger Dueck Sep 20 '19 at 16:30
@hjpotter92 - "If your older values are the same as your new ones, why would you need to update it in any case?" - Yes,updation is not required in such a case,but then how do we write a query using INSERT ON DUPLICATE KEY UPDATE clause without specifying any values for updation. When you insert a new row into a table and if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. To avoid this error, we use INSERT ON DUPLICATE KEY UPDATE. Now if we dont want to update values if duplication occurs and also not get the error then how can we form the query? Thanks. – Ayush Oct 29 '19 at 06:31
Doesn't answer the question. You could have code which doesn't care if the values are different or not. An update shouldn't fail when an insert would. – wilmol Sep 22 '20 at 23:16

score 53 · Answer 2 · edited May 23 '17 at 12:10

53

There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO

However it does not work quite the same as 'ON DUPLICATE UPDATE'

It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key

You can't reference the values in the old rows so you can't do an equivalent of

INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) 
ON DUPLICATE KEY UPDATE
id=1, a=2, b=3, c=c + 1;

I'd like to use the work around to get the ID to!

That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.

However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.

Someone else suggested before you can reduce some typing by doing:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

edited May 23 '17 at 12:10

Community

1
1

answered Jan 17 '13 at 16:38

Danack

24,939
16
90
122

So, I can't stick with the primary key before a `replace into` query? – Roy Jan 17 '13 at 16:43
1

No - that row is deleted and a new row created, which will have a new primary key. – Danack Jan 17 '13 at 16:47
wont this would slow the process on large datasets and like importing csv's with 100K or more rows? – Muhammad Omer Aslam May 14 '20 at 17:45
2

As a previous comment mentions, REPLACE INTO deletes and re-creates the row. As such, if you have a foreign key then the constraint will fail and, thus, the REPLACE INTO will fail. Your best best is ON DUPLICATE KEY UPDATE with VALUES() – phil-lavin Jan 26 '22 at 17:18

score 26 · Accepted Answer · answered Jan 18 '13 at 11:52

26

There is no other way, I have to specify everything twice. First for the insert, second in the update case.

answered Jan 18 '13 at 11:52

Roy

4,254
5
28
39

12

This is incorrect and should not be the accepted answer. The answer from @Danack is the correct answer. – Wayne Workman Aug 13 '18 at 19:33
4

You should re-read the question @WayneWorkman, this is the correct answer. – Roy Jul 16 '19 at 11:48

score 26 · Answer 4 · answered Feb 03 '16 at 03:42

Here is a solution to your problem:

I've tried to solve problem like yours & I want to suggest to test from simple aspect.

Follow these steps: Learn from simple solution.

Step 1: Create a table schema using this SQL Query:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` varchar(32) NOT NULL,
  `status` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `no_duplicate` (`username`,`password`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Step 2: Create an index of two columns to prevent duplicate data using following SQL Query:

ALTER TABLE `user` ADD INDEX no_duplicate (`username`, `password`);

or, Create an index of two column from GUI as follows:

Step 3: Update if exist, insert if not using following queries:

INSERT INTO `user`(`username`, `password`) VALUES ('ersks','Nepal') ON DUPLICATE KEY UPDATE `username`='master',`password`='Nepal';

INSERT INTO `user`(`username`, `password`) VALUES ('master','Nepal') ON DUPLICATE KEY UPDATE `username`='ersks',`password`='Nepal';

Thank you for this walkthrough - made me understand, highly appreciated, thank you! — Michael Nilsson, Oct 07 '17 at 08:30
FYI storing passwords in plain text is a terrible idea, as is trying to prevent duplicate passwords at the database level. — OrangeDog, Nov 21 '18 at 14:20

score 14 · Answer 5 · edited Nov 07 '17 at 15:52

14

Just in case you are able to utilize a scripting language to prepare your SQL queries, you could reuse field=value pairs by using SET instead of (a,b,c) VALUES(a,b,c).

An example with PHP:

$pairs = "a=$a,b=$b,c=$c";
$query = "INSERT INTO $table SET $pairs ON DUPLICATE KEY UPDATE $pairs";

Example table:

CREATE TABLE IF NOT EXISTS `tester` (
  `a` int(11) NOT NULL,
  `b` varchar(50) NOT NULL,
  `c` text NOT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

edited Nov 07 '17 at 15:52

Ivar

6,138
12
49
61

answered Dec 06 '16 at 17:23

Chris

893
10
23

4

You should be escaping your values or executing a prepared statement with your values. – Chinoto Vokro Jan 05 '17 at 10:07
1

@ChinotoVokro - I agree. This is just presenting a "how you could" and is not even using any query functions in the example. – Chris Jan 28 '17 at 01:24
1

This is a good alternative to specifying an array of keys and an array of values. Thanks. – Mark Carpenter Jr Oct 23 '17 at 13:25

score 8 · Answer 6 · answered Oct 07 '15 at 11:07

8

You may want to consider using REPLACE INTO syntax, but be warned, upon duplicate PRIMARY / UNIQUE key, it DELETES the row and INSERTS a new one.

You won't need to re-specify all the fields. However, you should consider the possible performance reduction (depends on your table design).

Caveats:

If you have AUTO_INCREMENT primary key, it will be given a new one
Indexes will probably need to be updated

answered Oct 07 '15 at 11:07

Matej

9,548
8
49
66

there is a constraint violation if the index is also a foreign key for another table, it's triggered by delete part. – user3290180 Jul 04 '16 at 09:47

score 8 · Answer 7 · edited Jun 20 '20 at 09:12

8

I know it's late, but i hope someone will be helped of this answer

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

You can read the tutorial below here :

https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/

http://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 20 '19 at 07:47

Galang Piliang

96
1
2

1

Good answer. But the use of VALUES() to refer to the new rows is [deprecated as of MySQL 8.0.20](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html). The new approach, detailed at the link, is to use an alias for the row. In this example, the alias is `new`: `INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;` – user697473 Apr 06 '20 at 12:33
@user697473 I'm getting the error: "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 'AS new ON DUPLICATE KEY UPDATE a = new.a'", and my query was working (except when duplicate keys occurred, obviously) before I implemented this clause. Any ideas? – aaron Jun 18 '20 at 21:04
1

@aaron - sorry, no great ideas. MySQL 8.0.20 was just released at the end of April; perhaps MariaDB hasn't caught up yet. That could explain why it's giving you an error. – user697473 Jun 18 '20 at 22:13
@user697473 Yeah I actually just tried the a = VALUES(a) method in the original answer and it worked, thanks anyways. – aaron Jun 19 '20 at 00:30
Im having the same trouble as @aaron, so Im obligated to use the VALUES function even knowing that it is deprecated. Any update on this problem? – alex55132 Sep 23 '20 at 13:54

score 1 · Answer 8 · answered Feb 18 '22 at 14:31

With MySQL v8.0.19 and above you can do this: mysql doc

INSERT INTO mytable(fielda, fieldb, fieldc) 
VALUES("2022-01-01", 97, "hello") 
AS NEW(newfielda, newfieldb, newfieldc) 
ON DUPLICATE KEY UPDATE
fielda=newfielda,
fieldb=newfieldb,
fieldc=newfieldc;

SIDENOTE: Also if you want a conditional in the on duplicate key update part there is a twist in MySQL. If you update fielda as the first argument and include it inside the IF clause for fieldb it will already be updated to the new value! Move it to the end or alike. Let's say fielda is a date like in the example and you want to update only if the date is newer than the previous:

INSERT INTO mytable(fielda, fieldb) 
VALUES("2022-01-01", 97) 
AS NEW(newfielda, newfieldb, newfieldc) 
ON DUPLICATE KEY UPDATE
fielda=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfielda,fielda),
fieldb=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfieldb,fieldb);

in this case fieldb would never be updated because of the <! you need to move the update of fielda below it or check with <= or =...!

INSERT INTO mytable(fielda, fieldb) 
VALUES("2022-01-01", 97) 
AS NEW(newfielda, newfieldb, newfieldc) 
ON DUPLICATE KEY UPDATE
fielda=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfielda,fielda),
fieldb=IF(fielda=STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfieldb,fieldb);

This works as expected with using = since fielda is already updated to its new value before reaching the if clause of fieldb... Personally i like <= the most in such a case if you ever rearrange the statement...

score -9 · Answer 9 · answered Jul 08 '15 at 09:44

-9

you can use insert ignore for such case, it will ignore if it gets duplicate records INSERT IGNORE ... ; -- without ON DUPLICATE KEY

answered Jul 08 '15 at 09:44

pitu

822
3
11
35

I wanted to insert it when not present, else update it. Not ignore it. – Roy Jul 08 '15 at 12:26
why you want to update it if you are updating with previous/same value, if its a new value then its ok with it, if not insert ignore work for it – pitu Jul 08 '15 at 13:06
2

Most likely because the values may change, and he wants to create a record if one does not exist, but update an existing record if it does (with changes) without the overhead of a round trip back to the application and then back to the database again. – mopsyd Aug 17 '15 at 22:34

On Duplicate Key Update same as insert

9 Answers9

Caveats:

Linked

Related