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.
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
ON DUPLICATE KEY UPDATE increase the affected rows by 2. It reports 0 if nothing is actually updated (same as the regular UPDATE). –
Sot ON DUPLICATE KEY UPDATE causes the autoincrement to increment even on updates, making this pretty much unusable if you need to do hundreds or thousands of these queries per day, since then the autoincrement will steadily increase by x rows every time even if no new rows are being added. –
Garrity INSERT INTO table SET col = val syntax, allows for copy and pasting between the 2 parts of the query. so the above would be: INSERT INTO table SET id = 1, name = "A", age = 19) ON DUPLICATE KEY UPDATE name = "A", age = 19 –
Pipsissewa Check out REPLACE:
REPLACEworks exactly likeINSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEYor aUNIQUEindex, the old row is deleted before the new row is inserted.
Example:
REPLACE INTO `tablename` (`id`, `name`, `age`) VALUES (1, "A", 19)
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),
...
VALUES(name) does not work, you can try name = 'name_value',...; –
Winger 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);
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.
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
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)
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.
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).
replace into does is exactly "insert into, or update when existing". @Owl –
Bart CREATE TRIGGER worklog_update AFTER INSERT ON worklog FOR EACH ROW REPLACE INTO support_time_monthly_hours ( ProjectID, monthTotalTime, Year, Month ) SELECT jiraissue.PROJECT, SUM(worklog.timeworked), YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE()) FROM worklog, jiraissue WHERE worklog.issueid = jiraissue.ID AND jiraissue.PROJECT = (SELECT PROJECT FROM jiraissue WHERE NEW.issueid = jiraissue.ID ) AND worklog.startdate BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01 00:00:00') AND NOW(); –
Often 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;
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 ...
INSERT INTOThe INSERT statement allows you to insert one or more rows into a table
INSERT INTO clause.VALUES keyword.INSERT INTO table_name(column_name1, column_name2, column_name3) VALUES("col_value_1", "col_value_2", "col_value_3");
INSERT INTO with WHERE NOT EXISTS clauseINSERT 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;
REPLACE INTOREPLACE 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");
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;
© 2022 - 2025 — McMap. All rights reserved.