They have: 121 posts

Joined: Aug 2008

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?


They have: 121 posts

Joined: Dec 2008

"INSERT INTO table (a,b,c) VALUES (1,2,3)


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's picture

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.

They have: 121 posts

Joined: Aug 2008

cool - i'll give that a go.

They have: 121 posts

Joined: Aug 2008

Works perfect. Thanks Shaggy!

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's picture

They have: 5 posts

Joined: Apr 2009

Thanks to this thread it really helped me.... for updating some of my content

Megatron's picture

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

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's picture

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

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
$update_query="update basket set total=total+'val1' where condition"; // total will be updated to total+number of new puppies added.

They have: 1 posts

Joined: Nov 2012

GOd I love this forum

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;

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.