Insert into a MySQL table or update if exists
742
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.
mysql sql insert-update
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
add a comment |
742
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.
mysql sql insert-update
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
3
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02
add a comment |
742
742
742
220
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.
mysql sql insert-update
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
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.
mysql sql insert-update
mysql sql insert-update
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
edited Jan 8 '17 at 13:06
Peter Mortensen
13.7k1986113
13.7k1986113
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
asked Nov 17 '10 at 14:08
KeshanKeshan
5,369103768
5,369103768
3
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02
add a comment |
3
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02
3
3
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02
add a comment |
10 Answers
10
active
oldest
votes
1392
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
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
I wonder whyaffected row countresults in2when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)
– Dimitry K
Feb 20 '14 at 15:17
13
This is a bit late, but anyway: it is stated in the manual that updates inON DUPLICATE KEY UPDATEincrease the affected rows by 2. It reports 0 if nothing is actually updated (same as the regularUPDATE).
– Vatev
Mar 31 '14 at 11:53
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
|
show 18 more comments
212
Check out REPLACE
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
|
show 2 more comments
32
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),
...
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
add a comment |
24
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
|
show 1 more comment
18
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.
edited Aug 27 '14 at 8:05
atur
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
add a comment |
9
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).
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
Whatreplace intodoes is exactly "insert into, or update when existing". @Owl
– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query: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();
– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
add a comment |
8
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 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
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
REPLACEis not documented in the linked document.
– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
add a comment |
6
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.
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
add a comment |
0
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 ...
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
add a comment |
-5
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` )
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
add a comment |
protected by Tushar Gupta Sep 1 '15 at 10:34
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
10 Answers
10
active
oldest
votes
10 Answers
10
active
oldest
votes
active
oldest
votes
active
oldest
votes
1392
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
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
I wonder whyaffected row countresults in2when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)
– Dimitry K
Feb 20 '14 at 15:17
13
This is a bit late, but anyway: it is stated in the manual that updates inON DUPLICATE KEY UPDATEincrease the affected rows by 2. It reports 0 if nothing is actually updated (same as the regularUPDATE).
– Vatev
Mar 31 '14 at 11:53
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
|
show 18 more comments
1392
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
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
I wonder whyaffected row countresults in2when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)
– Dimitry K
Feb 20 '14 at 15:17
13
This is a bit late, but anyway: it is stated in the manual that updates inON DUPLICATE KEY UPDATEincrease the affected rows by 2. It reports 0 if nothing is actually updated (same as the regularUPDATE).
– Vatev
Mar 31 '14 at 11:53
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
|
show 18 more comments
1392
1392
1392
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
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
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
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
edited May 12 '17 at 5:53
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
answered Nov 17 '10 at 14:12
DonnieDonnie
35.2k75377
35.2k75377
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
I wonder whyaffected row countresults in2when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)
– Dimitry K
Feb 20 '14 at 15:17
13
This is a bit late, but anyway: it is stated in the manual that updates inON DUPLICATE KEY UPDATEincrease the affected rows by 2. It reports 0 if nothing is actually updated (same as the regularUPDATE).
– Vatev
Mar 31 '14 at 11:53
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
|
show 18 more comments
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
I wonder whyaffected row countresults in2when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)
– Dimitry K
Feb 20 '14 at 15:17
13
This is a bit late, but anyway: it is stated in the manual that updates inON DUPLICATE KEY UPDATEincrease the affected rows by 2. It reports 0 if nothing is actually updated (same as the regularUPDATE).
– Vatev
Mar 31 '14 at 11:53
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
82
82
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.
– Andrew Ensley
May 11 '12 at 21:27
36
36
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.
– Keven
Dec 4 '13 at 21:12
6
6
I wonder why
affected row count results in 2 when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)– Dimitry K
Feb 20 '14 at 15:17
I wonder why
affected row count results in 2 when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)– Dimitry K
Feb 20 '14 at 15:17
13
13
This is a bit late, but anyway: it is stated in the manual that updates in
ON DUPLICATE KEY UPDATE increase the affected rows by 2. It reports 0 if nothing is actually updated (same as the regular UPDATE).– Vatev
Mar 31 '14 at 11:53
This is a bit late, but anyway: it is stated in the manual that updates in
ON DUPLICATE KEY UPDATE increase the affected rows by 2. It reports 0 if nothing is actually updated (same as the regular UPDATE).– Vatev
Mar 31 '14 at 11:53
43
43
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)
– Curious Sam
Mar 1 '16 at 10:07
|
show 18 more comments
212
Check out REPLACE
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
|
show 2 more comments
212
Check out REPLACE
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
|
show 2 more comments
212
212
212
Check out REPLACE
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
Check out REPLACE
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
edited Nov 17 '10 at 14:15
Konerak
32.8k978108
32.8k978108
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
answered Nov 17 '10 at 14:14
Martin SchapendonkMartin Schapendonk
8,82431324
8,82431324
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
|
show 2 more comments
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
9
9
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
@Piontek Because this one is shorter and easier to understand and no-one explained why "insert on duplicate" is better.
– Mr_Chimp
Jun 13 '13 at 13:55
62
62
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
it changes the IDs of the record and thus may destroy foreign references.
– boh
Sep 13 '13 at 5:32
85
85
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
The other problem with REPLACE INTO is that you must specify values for ALL fields...otherwise fields will get lost or replaced with default values. REPLACE INTO essentially deletes the row if it exists, and inserts the new row. In the example, if you did 'REPLACE INTO table (id, age) values (1, 19) then the name field would become null.
– Dale
Dec 8 '13 at 8:05
26
26
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
This is actually DELETE the entire row and perform new INSERT.
– mjb
Jun 12 '14 at 4:05
8
8
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
@mjb Hence you need to have DELETE privileges, which it's best not to have if you don't need them. My environment's database user only has INSERT and UPDATE permissions, so REPLACE won't work.
– ndm13
Jun 5 '15 at 0:42
|
show 2 more comments
32
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),
...
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
add a comment |
32
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),
...
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
add a comment |
32
32
32
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),
...
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
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),
...
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
edited Jan 27 '17 at 12:51
Stijn
16.4k1082127
16.4k1082127
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
answered Jan 27 '17 at 12:45
Fabiano SouzaFabiano Souza
45143
45143
add a comment |
add a comment |
24
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
|
show 1 more comment
24
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
|
show 1 more comment
24
24
24
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
answered Nov 17 '10 at 14:17
Luis ReyesLuis Reyes
32516
32516
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
|
show 1 more comment
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
6
6
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
actually i don't need to add the new values to another row with a new ID instead i want to replace the existing values of id = 1 with this values. (as i understand this increments the id and add the data)
– Keshan
Nov 17 '10 at 14:21
45
45
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
I don't think he wants to increase the id by one on duplicates.
– Donnie
Nov 17 '10 at 14:22
5
5
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
"transgress" is not the word you're looking for :) Unfortunately, now I've seen "transgress", I can no longer visualise the actual word..
– mwfearnley
Apr 13 '17 at 7:47
1
1
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
Were you looking for "traverses" or "covers"?
– Chris Dev
Jun 28 '17 at 13:52
2
2
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
@mwfearnley The word you were trying to visualise is "transcends". Only took a year and a half :-)
– Michael Krebs
Sep 3 '18 at 16:11
|
show 1 more comment
18
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.
edited Aug 27 '14 at 8:05
atur
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
add a comment |
18
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.
edited Aug 27 '14 at 8:05
atur
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
add a comment |
18
18
18
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.
edited Aug 27 '14 at 8:05
atur
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
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.
edited Aug 27 '14 at 8:05
atur
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
edited Aug 27 '14 at 8:05
atur
95062033
edited Aug 27 '14 at 8:05
atur
95062033
edited Aug 27 '14 at 8:05
atur
95062033
95062033
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
answered Aug 27 '14 at 7:42
Abu Mohammad RaselAbu Mohammad Rasel
2,31521927
2,31521927
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
add a comment |
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
I want to work without using id. Have you tried without primary key?
– ersks
Jul 6 '17 at 6:41
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
@ersks see the question. user asked about when there is an unique key
– Abu Mohammad Rasel
Jul 9 '17 at 8:44
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.
– ersks
Jul 13 '17 at 8:33
add a comment |
9
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).
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
Whatreplace intodoes is exactly "insert into, or update when existing". @Owl
– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query: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();
– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
add a comment |
9
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).
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
Whatreplace intodoes is exactly "insert into, or update when existing". @Owl
– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query: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();
– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
add a comment |
9
9
9
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).
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
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).
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
edited Jan 8 '17 at 13:08
Peter Mortensen
13.7k1986113
13.7k1986113
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
answered Nov 17 '16 at 7:11
DawnSongDawnSong
1,5081818
1,5081818
Whatreplace intodoes is exactly "insert into, or update when existing". @Owl
– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query: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();
– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
add a comment |
Whatreplace intodoes is exactly "insert into, or update when existing". @Owl
– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query: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();
– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
What
replace into does is exactly "insert into, or update when existing". @Owl– DawnSong
Nov 17 '16 at 15:38
What
replace into does is exactly "insert into, or update when existing". @Owl– DawnSong
Nov 17 '16 at 15:38
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
+1 ( 1 of 3 ) I found this to work for my situation - I needed to replace an existing row with a unique key or if not there then add the row. Simplest of solutions here.
– therobyouknow
Jan 30 '17 at 1:21
( 2 of 3 ) My query:
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();– therobyouknow
Jan 30 '17 at 1:22
( 2 of 3 ) My query:
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();– therobyouknow
Jan 30 '17 at 1:22
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
( 3 of 3 ) Related question/answer stackoverflow.com/questions/41767517/…
– therobyouknow
Jan 30 '17 at 1:23
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
The problem with this in mysql is that replace will remove other values in case they are not provided. However @fabiano-souza solution is more appropriate
– Quamber Ali
Mar 21 '18 at 13:37
add a comment |
8
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 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
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
REPLACEis not documented in the linked document.
– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
add a comment |
8
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 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
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
REPLACEis not documented in the linked document.
– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
add a comment |
8
8
8
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 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
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);
INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 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
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
edited Feb 15 at 22:40
Pritam Banerjee
10.9k64467
10.9k64467
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
answered Dec 7 '17 at 21:06
user2613580user2613580
24636
24636
REPLACEis not documented in the linked document.
– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
add a comment |
REPLACEis not documented in the linked document.
– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
REPLACE is not documented in the linked document.– Ray Baxter
Mar 12 '18 at 22:04
REPLACE is not documented in the linked document.– Ray Baxter
Mar 12 '18 at 22:04
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
sql queries full of typos, your examples won't work. It's INSERT INTO TABLE (not INTO TABLE) and ON DUPLICATE KEY UPDATE (not ON DUPLICATE UPDATE SET). Not sure who's upvoting this
– Robert Sinclair
Nov 2 '18 at 3:49
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
Sorry for the typos error. Thanks for correcting me
– user2613580
Nov 29 '18 at 9:48
add a comment |
6
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.
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
add a comment |
6
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.
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
add a comment |
6
6
6
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.
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
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.
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
answered Jun 9 '17 at 5:31
SteveCinqSteveCinq
565612
565612
add a comment |
add a comment |
0
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 ...
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
add a comment |
0
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 ...
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
add a comment |
0
0
0
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 ...
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
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 ...
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
edited Feb 5 at 10:40
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
answered Feb 5 at 9:55
Renish GotechaRenish Gotecha
614
614
add a comment |
add a comment |
-5
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` )
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
add a comment |
-5
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` )
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
add a comment |
-5
-5
-5
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` )
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
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` )
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
answered Dec 12 '14 at 11:21
SuddaSudda
3231417
3231417
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
add a comment |
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
Does that even work!? Have you tested it?
– Fandango68
Oct 28 '15 at 0:45
add a comment |
protected by Tushar Gupta Sep 1 '15 at 10:34
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
3
SQL needs an official syntax for this use case that doesn't force duplication of values in the syntax and preserves the primary key.
– Pete Alvin
Jan 26 '18 at 13:02