Knowledge Walls
John Peter
Pune, Maharashtra, India
What are all the ways to UPDATE existing rows and INSERT non-exist rows in a mysql table?
3338 Views
They are two ways to replace existing records 
(i) First way to insert or update by primary key

        REPLACE INTO table_name(id,username,age) VALUES(2,"john",18);

        It is replacing records by primary key. If 2 primary key is already exist in table then username and age going to update. otherwise 2 - id is not exist in table will insert id,username and age as another new record.


(ii) Second way to insert or update by unique key

      Create key columns as unique column in table. Take username as unique key column in table.
      INSERT INTO table_name (id,username,age) VALUES(2,"john",18) ON DUPLICATE KEY UPDATE age = values(age);

     If john is already exist in table then on duplicate key insert again with value 'john' then age is going to update in existing row. otherwise John record will get inserted.

Some ways to use "ON DUPLICATE KEY UPDATE"
-- Values can be directory assign on update
INSERT INTO table_name(id,username,qualification,age) VALUES(2,"John","mca",22)
ON DUPLICATE KEY UPDATE qualification = "mca", age = 22;

-- Values can be directory refer insert values to update
INSERT INTO table_name(id,username,qualification,age) VALUES(2,"John","mca",22)
ON DUPLICATE KEY UPDATE qualification = values(qualification), age = values(age);

-- Values add/concat existing column value with new value
INSERT INTO table_name(id,username,qualification,age) VALUES(2,"John","mca",22)
ON DUPLICATE KEY UPDATE qualification = concat(values(qualification),",mca"), age = age + values(age);
Best Lessons of "One day One Thing to Know"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details