Re: Update in loop

[Rasika Amarasiri]

Use 
"UPDATE host_status, hosts SET stat_status='B'where 
 host_status.stat_host=hosts.hostname and
hosts.hostname=\"$hostname\""

You havn't got a part in the where clause to say which
line to update.

Regards,

Rasika

 --- Werner Otto <wot@stripped> wrote: > Hi,
> 
> I have a case where I need to update while in a
> loop.
> 
> My code:
>
------------------------------------------------------------
> 1  $db="test";
> 2  $host="test";
> 3  $port="33306";
> 4  $userid="test";
> 5  $passwd="test";
> 6 
>
$connectionInfo="DBI:mysql:database=$db;$host:$port";
> 7  $dbh =
> DBI->connect($connectionInfo,$userid,$passwd) ||
> die("Could not 
> get connected!");
> 8
> 9  $query = "SELECT * FROM hosts,host_status where 
> hosts.hostname=host_status.stat_host";
> 10 $sth = $dbh->prepare($query);
> 11 $sth->execute();
> 12 
>
my($hostname,$hostip,$stat_host,$stat_ip,$stat_stu_dte,$stat_stu_tme,$stat_tud_dte,$stat_tud_tme,$stat_std_dte,$stat_std_tme,13
> 
> $stat_td_dte,$stat_td_time,$stat
>     _status);
> 14 
>
$sth->bind_columns(\$hostname,\$hostip,\$stat_host,\$stat_ip,\$stat_stu_dte,\$stat_stu_tme,\$stat_tud_dte,\$stat_tud_tme,
>    
>
\$stat_std_dte,\$stat_std_tme,\$stat_td_dte,\$stat_td_time,\$stat_status);
> 15
> 16 while($sth->fetch())
> 17 {
> 18   $status = `ping $hostname 1`;
> 19   if($status =~ /alive/)
> 20   {
> 21      $con_stat = "G";
> 22   }
> 23   else
> 24   {
> 25      $con_stat = "B";
> 26      query = "UPDATE host_status, hosts SET
> stat_status='B'where 
> host_status.stat_host=hosts.hostname";
> 27      my $sth = $dbh->prepare($query);
> 28      $sth->execute();
> 29   }
> 30}
>
---------------------------------------------------------------
> My problem is at line 26. I have a list of 5 entries
> in my hosts table. I 
> have the same amount of entries in my host_status
> table. I only have one 
> host that is down at the moment and would like to
> update only the status 
> field for that particular host.
> At the momemnt it is updating all of them.
> 
> Could someone please stear me in the correct
> direction?
> 
> -- 
> Kind Regards,
> Otto
> 
> -- 
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:   
>
http://lists.mysql.com/perl?unsub=1
>  


	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger -
sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com