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
Web design and development from i am root
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.
pr0gr4mm3r posted this at 14:56 — 17th February 2009.
He has: 1,505 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
LisaRole posted this at 22:12 — 8th May 2009.
They have: 19 posts
Joined: Feb 2009
I tried to follow, It didn't work for me.
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.
Birkenstock posted this at 09:51 — 5th May 2011.
They have: 7 posts
Joined: Apr 2011
I just come to learn
jhnbrwn 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
Get Elegant Magento Themes
Get your websites optimised by SEO London
mattgoes posted this at 06:53 — 21st June 2011.
They have: 8 posts
Joined: Jun 2011
soor about that i can not give you a favor,but i believe that you will get a satifactory solution soon .
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.