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

Answer 1

Here is a better way to accomplish an INSERT and UPDATE without affecting the auto increment and on top of that getting back the primary key in either scenario in a consistent way. It does involve multiple commands but can be done on one line if desired. This would be in a case where one or more fields make up a unique constraint like in this case country.

If you want the primary key back from INSERT or UPDATE only null values

SET @now=NOW();
SET @country='Example Country',@country_code='EC',@country_overview='An example country overview.';
INSERT INTO country (country, country_code, country_overview, added_at, updated_at)
SELECT @country,@country_code,@country_overview, @now, @now
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM country WHERE country=@country
);
SELECT countryid INTO @countryid FROM country WHERE country=@country;
UPDATE country 
SET 
country_code = @country_code, 
country_overview = @country_overview, 
updated_at = @now 
WHERE countryid = @countryid AND added_at <> @now;

If you want the primary key back from INSERT or UPDATE only null values

SET @now=NOW();
SET @country='Example Country',@country_code='EC',@country_overview='An example country overview.';
INSERT INTO country (country, country_code, country_overview, added_at, updated_at)
SELECT @country,@country_code, @now, @now
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM country WHERE country=@country
);
SELECT countryid INTO @countryid FROM country WHERE country=@country;
UPDATE country 
SET 
country_code = COALESCE(country_code, @country_code), 
country_overview = COALESCE(country_overview, @country_overview), 
updated_at = @now 
WHERE countryid = @countryid AND added_at <> @now;

There are few other ways you can work with this to get the primary key back you could do the following as your last statement.

SELECT @countryid;

Answer 2

Check out REPLACE:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Example:

REPLACE INTO `tablename` (`id`, `name`, `age`) VALUES (1, "A", 19)

Answer 3

Following are some of the possible approaches:

Using INSERT INTO

The INSERT statement allows you to insert one or more rows into a table

• First, specify the table name and a list of comma-separated columns inside parentheses after the INSERT INTO clause.
• Secondly, put a comma-separated list of values of the corresponding columns inside the parentheses following the VALUES keyword.

INSERT INTO table_name(column_name1, column_name2, column_name3) VALUES("col_value_1", "col_value_2", "col_value_3");

Using INSERT INTO with WHERE NOT EXISTS clause

INSERT INTO table_name (column_name_1, column_name_2, column_name_3)
SELECT * FROM (SELECT "col_value_1", "col_value_2","col_value_3") AS tmp_name
WHERE NOT EXISTS (
    SELECT column_name2 FROM table_name WHERE column_name = "sample_name"
) LIMIT 1;

Using REPLACE INTO

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE INTO table_name(column_name1, column_name2, column_name3) VALUES("col_value_1", "col_value_2", "col_value_3");

Answer 4

Any of these solution will work regarding your question:

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

or

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

or

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

Answer 5

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 < b.id 
AND a.name <=> b.name AND a.age <=> 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)

Answer 6

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;

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

Try this out:

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

Hope this helps.