CODEWITHSUNDEEP
databaseperformanceupdates
Avisek Chakraborty
Avisek Chakraborty

Reputation: 8309

MySQL: Direct Update or Select before a conditional Update?

Just curious to know- if we need to do a conditional Update in a large table, then which is the best approach-

Directly doing an Update or check for existing entry before Update.

function doDirectUpdate()
{
   // UPDATE table WHERE condn
}

OR

function doCheckAndUpdate()
{
   // SELECT COUNT(id) AS exist FROM table WHERE condn
   if(id exists)
   {
      // UPDATE table WHERE condn
   }
   else
   {
      echo 'No matching entry';
   }
}

Upvotes: 0

Views: 273

Answers (1)

Anthony Ledesma
Anthony Ledesma

Reputation: 573

One should not perform both a SELECT, then a conditional UPDATE, simply to display the number of matching rows -- or lack of matching rows. The only time that you should perform the SELECT is if you have other logic that states to not update if there are more than X matching rows.

UPDATE returns the number of rows that were updated, and therefor matched. You should take the return value, from UPDATE, then alert if there are no matching entries.

function doUpdateAndAlertIfNotMatched()
{
   numberOfRowsUpdated = UPDATE table WHERE condn;
   if(numberOfRowsUpdated == 0)
   {
      echo 'No matching entry';
   }
}

reference: http://dev.mysql.com/doc/refman/5.0/en/update.html#id844302

Upvotes: 1

Related Questions