I recently had to update one table based on a select statement, where NULL was a
valid value for many of the fields. I ended up with the following monstrosity:

use DBI; my $driver="mysql"; my $database="legacy"; my $dns = "DBI:$driver:database=$database"; my $db=DBI->connect($dns); end if not defined $ARGV[0]; my $city=$ARGV[0]; my $streets=$db->prepare("select count(DISTINCT city) as cities, city, stdir,
street, strtype from pctraw group by street, stdir, strtype having cities=1 and city=?");

my $fixdst = $db->prepare("Update list set city=? where strdir=? and strname=?
and strtype=?"); my $fixds = $db->prepare("Update list set city=? where strdir=? and strname=?
and strtype is null"); my $fixst = $db->prepare("Update list set city=? where strdir is null and
strname=? and strtype=?"); my $fixs = $db->prepare("Update list set city=? where strdir is null and
strname=? and strtype is null"); my ($c, $u); $streets->execute($city); while (my $rec = $streets->fetchrow_arrayref) { my $q; $c++; my ($count, $ci,$dir,$name,$type) = @$rec; if (defined $dir and defined $type) { $q = $fixdst->execute($city,$dir,$name,$type); } if (defined $dir and not defined $type) { $q = $fixds->execute($city,$dir,$name); } if (not defined $dir and defined $type) { $q = $fixst->execute($city,$name,$type); } if (not defined $dir and not defined $type) { $q = $fixs->execute($city,$name); } print "$dir ** $name ** $type === $q\n" if $q > 0; $u += $q; } $db->disconnect; print "$c streets in $city checked\n"; print "$u recrods updated\n";

Is there a cleaner way to do that? When the number of potentially null fields
gets high, writing that sort of a filter becomes very confusing and hard to
maintain...