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

User · Answer

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

User · Answer

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  mdash  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here is a solution to your problem   I ve tried to solve problem like yours  amp  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

User · Answer

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

User · Answer

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

[mysql] On Duplicate Key Update same as insert

Caveats:

Examples related to mysql

Examples related to on-duplicate-key