MySQL INSERT UPDATE
Hey guys,
I'm looking for an efficient method of executing the following pseudo-code in mysql.
if (exist row where id == $id) {
UPDATE table SET value = '$value' WHERE id = '$id';
} else {
INSERT INTO table (id, value) VALUES ('$id', '$value');
}I thought there was a MySQL statement called INSERT UPDATE, but I must be mistaken. What's the easiest way to achieve this?
serpico
Shaggy posted this at 14:45 — 17th February 2009.
They have: 121 posts
Joined: Dec 2008
"INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;"
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
Cheers,
Shaggy.
lynnabbyy posted this at 07:36 — 22nd June 2013.
They have: 3 posts
Joined: Jun 2013
If you specify ON DUPLICATE KEY UPDATE , and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY
pr0gr4mm3r posted this at 14:56 — 17th February 2009.
He has: 1,502 posts
Joined: Sep 2006
Didn't know that was possible. I always used the conditional method that serpico posted. I will have to remember this.
serpico posted this at 21:14 — 17th February 2009.
They have: 121 posts
Joined: Aug 2008
cool - i'll give that a go.
serpico posted this at 08:20 — 18th February 2009.
They have: 121 posts
Joined: Aug 2008
Works perfect. Thanks Shaggy!
altweb posted this at 03:58 — 23rd March 2009.
They have: 14 posts
Joined: Nov 2005
Thats going to save a lot of time! I should be reading the mysql docs instead of the forums. Thanks for the tip!
n1tr0b posted this at 12:22 — 24th April 2009.
They have: 5 posts
Joined: Apr 2009
Thanks to this thread it really helped me.... for updating some of my content
Megatron posted this at 18:07 — 21st May 2009.
They have: 2 posts
Joined: May 2009
You have to have a relatively newer MySQL server... I think on duplicate key became available on mysql 4.1
adairace posted this at 12:44 — 23rd December 2009.
They have: 2 posts
Joined: Dec 2009
i think shaggy has posted the most exact answer near to me.
i really appreciate his little effort.
jessicaellen posted this at 01:39 — 6th May 2011.
They have: 31 posts
Joined: Nov 2010
Dear i m sending you two mysql queries just check it, hope you will be satisfied.
1.insert into myUserTable (userId,login_time) values(3421,432432);
update myUserTable set logged_counts=logged_counts+1;
2.insert into myUserTable (userId,login_time)values(3421,3122) on DUPLICATE KEY UPDATE logged_counts=logged_counts+1;
-- if the table is empty it will insert 0 as the first value, as long as u do not have default as 1.
just try it
David26 posted this at 12:21 — 15th March 2012.
They have: 10 posts
Joined: Mar 2012
$insert_query="insert into puppybasket (number_of_puppies,color) values ('val1','val2')"; //val1 has the number and val2 has the color
mysql_query($insert_query);
$update_query="update basket set total=total+'val1' where condition"; // total will be updated to total+number of new puppies added.
mysql_query($update_query);
Philadelphia Sunglasses
princeromoe posted this at 12:06 — 2nd November 2012.
They have: 1 posts
Joined: Nov 2012
GOd I love this forum
RohanJaiswal posted this at 05:48 — 24th April 2013.
They have: 11 posts
Joined: Apr 2013
for insert- insert into tablename values('abs', 23, 'retye', 30);
for update-update tablename set attribute1=value1 where some_attribute=some_value;
AVG free antivirus
features of the unix
Default operating system
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.