# Assuming:
#
# Table1
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | 1 | Bill |
# +-----+-----------+
# | 2 | Bob |
# +-----+-----------+
# | 3 | Mike |
# +-----+-----------+
#
# Table2 before
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | ? | Mike |
# +-----+-----------+
# | ? | Bob |
# +-----+-----------+
# | ? | Bill |
# +-----+-----------+
#
# Table2 after
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | ? | 3 |
# +-----+-----------+
# | ? | 2 |
# +-----+-----------+
# | ? | 1 |
# +-----+-----------+
####
#!/usr/local/bin/perl -w
# print "Content-type: text/html\n\n";
use lib "/home/champ1/public_html/cgi-bin";
use warnings;
use CGI::Carp qw(fatalsToBrowser);
use strict;
use CGI qw/:cgi/;
use Data::Dumper;
use Common;
use Validate;
use HTML::Template;
use HTML::FillInForm;
$|=1;
#------------------------- declare --------------------------
my $query = new CGI;
my ($sth, $stmt, $template, $hdrflg, $fvalues); #$hdrflg = is set when a header is first written
my ($dbh, $keylock, $basehref) = dbconnect(); #f
$stmt = "SELECT id, name FROM table1";
$sth = execute_it($dbh,$stmt);
my $table1 = $sth->fetchall_arrayref({});
my $max_rows = 2;
my ($i, $j);
$stmt = "SELECT id, name FROM table2";
$sth = $dbh->prepare($stmt);
$sth->execute();
while (my $table2 = $sth->fetchall_arrayref(undef, $max_rows))
{
for $i ( 0 .. $#$table2 ) {
for $j ( 0 .. $#$table1 ) {
###line 44### if ( $table2->[$i]{'name'} eq $table1->[$j]{'name'} ) {
$stmt = ("UPDATE table2 SET name = ? WHERE id = '$table2->[$i]{'id'}'");
$sth = $dbh->prepare($stmt);
$sth->execute ( $table1->[$j]{'id'});
}
}
}
};
close_db();
exit();
##
##
$stmt = "SELECT id, name FROM table1";
$sth = execute_it($dbh,$stmt);
my $table1 = $sth->fetchall_arrayref({});
my ($i, $j, $sth2);
$stmt = "SELECT id, name FROM table2";
$sth = $dbh->prepare($stmt);
$sth->execute();
print "Content-type: text/plain \n\n";
my $max_rows = 500;
my $rows = []; # cache for batches of rows
while ( my $row = ( shift( @$rows ) || shift( @{$rows=$sth->fetchall_arrayref({},$max_rows) || []} ) ) )
{ for ( $row ) {
for $j ( 0 .. $#$table1 ) {
if ( $row->{'name'} eq $table1->[$j]{'name'} ) {
$stmt = qq/UPDATE table2 SET name = ? WHERE id = "$row->{id}"/;
print "Here.$j".Dumper ($stmt)."\n";
$sth2 = $dbh->prepare($stmt);
$sth2->execute ( $table1->[$j]{'id'});
}
}
}
};
close_db();
exit();