Insert into a MySQL table or update if exists

Question

I want to add a row to a database table  but if a row exists with the same unique key I want to update the row  For example  INSERT INTO table name  ID  NAME  AGE  VALUES 1   quot A quot   19    Let   s say the unique key is ID  and in my Database  there is a row with ID   1  In that case  I want to update that row with these values  Normally this gives an error  If I use INSERT IGNORE it will ignore the error  but it still won   t update

User · Answer

Check out REPLACE  http   dev mysql com doc refman 5 0 en replace html  REPLACE into table  id  name  age  values 1   A   19

User · Answer

Try this      INSERT INTO table  id name age  VALUES  1   Mohammad   21   ON DUPLICATE KEY UPDATE name  Mohammad  age  21     Note  Here if id is the primary key then after first insertion with id  1  every time attempt to insert id  1  will update name and age  and previous name age will change

User · Answer

In case  you want to keep old field  For ex  name   The query will be   INSERT INTO table  id  name  age  VALUES 1   A   19  ON DUPLICATE KEY UPDATE     name name  age 19

User · Answer

When using SQLite   REPLACE into table  id  name  age  values 1   A   19    Provided that id is the primary key  Or else it just inserts another row  See INSERT  SQLite

User · Answer

Any of these solution will work regarding your question  INSERT IGNORE INTO table  id  name  age  VALUES  1   quot A quot   19    or INSERT INTO TABLE  id  name  age  VALUES 1   quot A quot   19       ON DUPLICATE KEY UPDATE NAME    quot A quot   AGE   19     or REPLACE INTO table  id  name  age  VALUES 1   quot A quot   19    If you want to know in details regarding these statement visit this link

User · Answer

In case that you wanted to make a non-primary fields as criteria condition for ON DUPLICATE  you can make a UNIQUE INDEX key on that table to trigger the DUPLICATE   ALTER TABLE  table  ADD UNIQUE  unique index   name      And in case you want to combine two fields to make it unique on the table  you can achieve this by adding more on the last parameter   ALTER TABLE  table  ADD UNIQUE  unique index   name    age      Note  just make sure to delete first all the data that has the same name and age value across the other rows   DELETE table FROM table AS a  table AS b WHERE a id  lt  b id  AND a name  lt   gt  b name AND a age  lt   gt  b age    After that  it should trigger the ON DUPLICATE event   INSERT INTO table  id  name  age  VALUES 1   A   19  ON DUPLICATE KEY UPDATE     name   VALUES name   age   VALUES age

User · Answer

Just because I was here looking for this solution but for updating from another identically-structured table  in my case website test DB to live DB    INSERT  live-db table1 SELECT    FROM    test-db table1 t ON DUPLICATE KEY UPDATE         ColToUpdate1   t ColToUpdate1          ColToUpdate2   t ColToUpdate2                As mentioned elsewhere  only the columns you want to update need to be included  after ON DUPLICATE KEY UPDATE   No need to list the columns in the INSERT or SELECT  though I agree it s probably better practice

User · Answer

Try this out   INSERT INTO table  id  name  age  VALUES  1   A   19  ON DUPLICATE KEY UPDATE id   id   1    Hope this helps

User · Answer

When using batch insert use the following syntax   INSERT INTO TABLE  id  name  age  VALUES  1   A   19    2   B   17    3   C   22  ON DUPLICATE KEY UPDATE     name   VALUES  name

User · Answer

Use INSERT     ON DUPLICATE KEY UPDATE  QUERY   INSERT INTO table  id  name  age  VALUES 1   A   19  ON DUPLICATE KEY UPDATE     name  A   age 19

User · Answer

In my case i created below queries but in the first query if id 1 is already exists and age is already there  after that if you create first query without age than the value of age will be none   REPLACE into table SET  id    1   name     A    age    19   for avoiding above issue create query like below  INSERT INTO table SET  id     1    name     A    age    19 ON DUPLICATE KEY UPDATE  id     1    name     A   age    19   may it will help you

[mysql] Insert into a MySQL table or update if exists

Examples related to mysql

Examples related to sql

Examples related to insert-update

Examples related to upsert