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