如果存在，则插入到 MySQL 表或更新中

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 (id, name, age) values(1, "A", 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.

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

Check out REPLACE

http://dev.mysql.com/doc/refman/5.0/en/replace.html

REPLACE into table (id, name, age) values(1, "A", 19)

Try this out:

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Hope this helps.

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.

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Also do not forget to concern about the unique key constraint.

ALTER TABLE `table` ADD UNIQUE `unique_key` ( `id` ) 

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

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),
    ...

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.

INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);

INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE UPDATE SET NAME = "A", AGE = 19;

REPLACE INTO table (id, name, age) VALUES(1, "A", 19);

All these solution will work regarding your question.

If you want to know in details regarding these statement visit this link