MySQL if exist update else insert

hi,

I whant to write single sql for this:

if not exists (table colum value=something) insert new row else update table colum value

i know that MSSQL has this method, but how to do this in mysql?

 

easy way would be to search that data!
if the data is found then update it else insert a new row

 

What you want to use is the:

INSERT ON DUPLICATE KEY UPDATE


http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html


This only works for MySQL 5.0+

 

thanks for answer but i'm using 4.1 mysql version

 

OK try this:

$result = mysql_query("update test set col='test' where col_id='1';");		 
if (mysql_affected_rows()==0) {
	$result = mysql_query("insert into test (col_id, col) values ('1','test');");
}

Code (markup):

 

Thanks. I see that there is not any other way so i use this method.

 

That is the way unless you want to perform a select before you do an update or insert to check if the data exists. That's just a waste of processing though.

... my pleasure.

 

Sorry for digging this up, to help those searched through google for a simpler solutions that uses only 1 MySQL query instead of 2 which significantly reduces server load, you can just use REPLACE INTO or ON DUPLICATE KEY UPDATE

 

This works for most cases.

You can also use 'Insert Ignore' in some cases.

 

Try with REPLACE

 

I think this will not help in all cases. Suppose, if you are going to update the same values... ie, the new value & the old value are same...

the mysql_affected_rows will return 0.


When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

 

Beware that mysql_affected_rows() will return 0 if the value you're updating with is the same as in the database. MySQL is clever and realizes that no update is needed. So you won't know if the row exists or if it already had de same values.

What I usually do is that I create a column called "updated" as a timestamp. Then i always add updated = '".date('Y-m-d H:i:s')."' in the update query.
That way you always know that mysql_affected_rows() return 1 if the row exists.

 

Thanks a lot finally it worked

 

great to know you was able to figure it out after so many efforts thanks to all the DP members who were so kind sharing all these valuable information about mysql coding

 

sorry to ask on existing thread, but how will you use this query when you are getting input value as an array.

 

Use fetch_array with while

while($row = mysql_fetch_array($result)){
// code here
}

 

older sql version do not have this functionality.

i think the 5.xx or higher would be working for it
which version are u on for ur server