Update the record if exists.

[girish.kc]

Hi,

I am trying to update the status of the record. I don't want to insert the record if not exists. I just want to know the update is success or not.

 

I tried using affected_rows but it returns 0 if the existing status is same as the new status or if no record exists.

 

Please help.

 

[Zyx]

Which database system? For MySQL, you have the following construct:

 

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

[requinix]

If you can't use mysql_affected_rows() then do a SELECT beforehand.

[girish.kc]

Thanks for the replay.

@Zyx: I don't want to insert the record if not exists. So can't use 'Insert on Duplicate'

@requinix: I am settled for that now. But I'm trying to avoid the 'Select' before running the 'Update'. The reason is load. I have to update few hundred records every 3 mins.

There may be [may not be also] few non-existing records. So the 'Select' will be an unnecessary burden on the db server.

[gizmola]

Thanks for the replay.

@Zyx: I don't want to insert the record if not exists. So can't use 'Insert on Duplicate'

@requinix: I am settled for that now. But I'm trying to avoid the 'Select' before running the 'Update'. The reason is load. I have to update few hundred records every 3 mins.

There may be [may not be also] few non-existing records. So the 'Select' will be an unnecessary burden on the db server.

 

Your preconceptions are highly flawed.  MySQL is in general optimized for select and insert activity.  A select is always preferable to an update, and in fact there is nothing that reduces concurrency more than update activity, since it has to place a write lock on rows, indexes and often the table itself.

 

I can't imagine what your application is that would throw away inserts, but be interested in "non" updates to rows.

 

Last but not least, updating a couple of hundred rows shouldn't be a big deal unless this is a significantly large database, and again, updates reduce concurrency, but should allow reads to continue unless the entire table is locked.

[girish.kc]

@gizmola: Thanks for the info.

 

I'm not throwing away the insert, but don't want to try 'Insert on Duplicate Update'

 

Because.

I am working on an application which gets the status update from many ( in 100's now and may be 1000's later) sources. I just want to store the latest status of each source in a table for the report generating purpose. So I'm updating ONLY the status field of the table.

If the update fails [non-existing record],  I have to fetch the other info related to the source from another table and insert it.

 

So for handling few non-existing records I can't do one more query to build the record.

 

The flow is as follows:

 

• Receive the status from the source
  
• update the status field 'Status' table  for the source.
  
• if not exists
   
  • 
       
• Get the source details from 'Source' table
     
• Insert into 'Status' table along with the new status
     

 

 

 

[gizmola]

You can insert all the rows for the update into a temp table, and then join the temp table to the status table with an inner join on whatever key you're using to identify a status row.  This same table can be used to outer join as the basis for a result set that shows you which rows need to be processed as inserts into the status table because they don't exist yet.

 

The other possibility is to write an insert trigger that would allow you to do the more advanced processing needed on inserts.  Since you describe this as an exception, a trigger sounds like a good solution, and if you were using a database like Oracle, would probably be the recommended solution to a problem like the one you describe. 

[girish.kc]

The first option looks very complicated and may not be practical in my case. Because every source will send it's status every 3 mins.

 

Triggers.. Yes I can try this.

 

Thanks again.