updating records after some time interval

[mangel.murti]

i have 18000 records in my table
i want to update a single field
and i want that first 500 records update then after some sleep next records have to updated in mysql

any idea appre

thankx

[jkon]

You can do it in many ways.
Programmatically (you didn’t mentioned what the trigger will be, an action or it is something that will occur in specific time?)
In later case you can use a series of cron jobs.
MySQL has also the sleep function that you can use it alone or in a stored procedure.

[mangel.murti]

You can do it in many ways.
Programmatically (you didn’t mentioned what the trigger will be, an action or it is something that will occur in specific time?)
In later case you can use a series of cron jobs.
MySQL has also the sleep function that you can use it alone or in a stored procedure.

acctu i have a table where i have broker id field, i have to update that broker id in lets say abc table where 10000 records exits or may be more ....

so i write this code that update all records but i need
500 records first update then after some time next 500 records should be updated...and so on.....
can i do it with out using cron .....?

please provide me some code and idea?

any help appre..

function update(){
                  
                 $obj_model_ext_property = new model_ext_property();
                 $SQL="SELECT id from ext_property";
                 

$rsset=$obj_model_ext_property->execute("SELECT",false,$SQL);
           $totalid=count($rsset);
                 // list of id bro....
                 $list_broker=array('1','2','3','8','11','9');
                 $coun=0;
                 for($i=0;$i<$totalid;$i++)
                 {   
               //$counter = $i;
               if($counter>5)
               {
                 $counter = 0;
               }
            
              
               $SQL="update ext_property set 

local_broker='".$list_broker[$counter]."' where 

id='".$rsset[$i]['id']."'";
            $result=$obj_model_ext_property->execute("",false,$SQL);
               $counter++;
               $coun++;
           }
                $this->app->assign("msg1", "Total " . $coun . " local 

Broker inserted Successfully...!!!");
               
          }
Q

thankx

[jkon]

What the trigger of this will be, it is an action that a user or administrator will do or it is something that will happen at a specific time of each day?

Maybe the right question is why and not how. Why are you thinking a solution that the next update will occur after x seconds? Describe the problem that drove you to that solution and maybe it has a cleaner (and easier) approach to solve it.

(Just out of curiosity the class “model_ext_property” that you are instantiating is a part of a framework or your own?)

[mangel.murti]

What the trigger of this will be, it is an action that a user or administrator will do or it is something that will happen at a specific time of each day?

Maybe the right question is why and not how. Why are you thinking a solution that the next update will occur after x seconds? Describe the problem that drove you to that solution and maybe it has a cleaner (and easier) approach to solve it.

(Just out of curiosity the class “model_ext_property” that you are instantiating is a part of a framework or your own?)

yes i am following mvc structure....
acctualyy when i am executing above function mysql generating error , my quries or total quries is above 15000 , so mysql generating Error .....
we have shared hosting......i want when first update loop execu.. 500 records update then a pause or sleep for x second then next 500...so on

[jkon]

I will describe you what I understood that the problem is (and correct me if I got it wrong). You have a table ext_property that has id and local_broker (maybe there are more there but let’s keep what we need). Maybe for reindexing (rows deleted for example) reasons you want the administration to fire a job that will take all rows and give values to local_broker depending on their row number (not the id – but order by id ascending). For example row 1 => 1 , 2 => 2 , 3 => 3 , 4 => 8 , 5 => 11 , 6 => 9 (… loop values) , 7 => 1 , 8 => 2 , 9 => 3 , 10 => 9 , 11 => 11 , 12 => 9 and so on.

If this is the case you can do it with just one statement but let’s see it in more easy way with 6 statements.

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 0) AS eid ON ext.id = eid.id SET ext.local_broker = 1

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 1) AS eid ON ext.id = eid.id SET ext.local_broker = 2

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 2) AS eid ON ext.id = eid.id SET ext.local_broker = 3

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 3) AS eid ON ext.id = eid.id SET ext.local_broker = 8

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 4) AS eid ON ext.id = eid.id SET ext.local_broker = 11

UPDATE ext_property AS ext RIGHT JOIN (SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id FROM ext_property t,(SELECT @row := 0) r ORDER BY ID ASC) AS a WHERE MOD((a.ROW_NUM+5),6) = 5) AS eid ON ext.id = eid.id SET ext.local_broker = 9

Explaining a little what happens here,
MySQL don't have ROW_NUMBER() by default (to my knowledge) so we in a we use “SELECT a.id FROM (SELECT @row := @row + 1 as ROW_NUM,t.id” we use the @row to get the ROW_NUM and the id (defining that the first it will start from 0 “SELECT @row := 0” so the first one will be 1).
Next we take only the rows that their remainder of row number to 6 is 0 (we add 5 to row number to take rows 1,7,13 and so on…)
And last but not least we update with RIGHT JOIN and not using “IN” clause because in latter case we fire as many queries as rows.

I am not data manager , so I believe all these could be written better (please feel free to suggest cleanest ways). If I knew your programme I could suggest better code in programme or even a better data structure from the scratch to avoid all these).