[mysql] On Duplicate Key Update same as insert

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.

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/

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';

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.

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;

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

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

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'

1. 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

2. 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`);