SQL update vs. insert

[httpwebwitch]

Here's a great coding tip.

Ever find yourself selecting a row, to see if it exists, just to know whether to insert or update?

$result = mysql_query("select id from table where id = 123");
if(mysql_num_rows($result)){
 mysql_query("update table set name = 'foo' WHERE id = 123");
}else{
 mysql_query("insert into table (id,name) values (123,'foo')");
}

try this, instead.

mysql_query("update table set name = 'foo' WHERE id = 123");
if(!mysql_affected_rows()){
 mysql_query("insert into table (id,name) values (123,'foo')");
}

mysql_affected_rows() returns the number of rows that were changed by the previous query.

So if no rows were updated in the UPDATE, then you know the id 123 doesn't exist. The zero returned evaluates to false, and you insert the row.

It saves one redundant SELECT, and only does the INSERT if the UPDATE does nothing. It's easier to code, looks more elegant, and runs faster too.

[Anyango]

Coool, that's nice

[enigma1]

In my experience and based on the table type like InnoDB, having an update instead of the select takes longer. That is if the update fails and you need to do the insert.

Another way is using:
INSERT INTO <table> (cols) VALUES (values) ON DUPLICATE KEY UPDATE ...

if there is a key in the table.

[maximillianos]

There is a REPLACE command that I think does something very similar.

[httpwebwitch]

@enigma1,
that's a fabulous method I'd never heard of before

[dev.mysql.com...]

[Matthew1980]

Hi all,

Nifty, I think is the word to use here guy's! That's one for the snippets folder!

Cheers,
MRb

[httpwebwitch]

caveat to this method:

If you do an UPDATE and you pass in the same stuff that's already there, eg:

the database contains "123", and you do

UPDATE table SET col="123" WHERE key='foo'

then nothing really updates because the data didn't change

mysql_affected_rows() will return zero,

that evaluates to false,

and then your script will attempt to INSERT.

If you have unique or primary keys involved then errors happen, or worse: you get duplicate rows