mysql update script missing rows

[galorin]

I don't know if my error is in my SQL or in my PERL, but when I run this script, rows that should be caught, are being left out. About 1,100 rows out of about 11,000 are being missed.

#!/usr/local/bin/perl -w
use DBI;
#mysql config variables
$user = "me";
$pw = "secret";

$sel = "select `FM Reference`, HouseType.ht_id from hoursworked, HouseType where hoursworked.`FM Reference` = HouseType.design_production_no"; 

$db_bdb= DBI->connect('dbi:mysql:database=contacts;host=localhost:3306', $user, $pw, { RaiseError => 1, AutoCommit => 1 });
$rows = $db_bdb->selectall_arrayref($sel);


foreach $row(@$rows){
$sql = "UPDATE hoursworked SET ht_id = @$row[1] WHERE `FM Reference` = '@$row[0]';";
print "$sql\n";
}

 

[stevexff]

It looks like your SQL. The join only finds rows where the FMref = dp_no. If there are any rows in either table that don't meet this criterion they won't be listed.

Don't forget that the UPDATE statement acts on the set of rows that meet the criteria, and unless 'FM reference' identifies a unique row you could hit multiple rows. Of course, this could be what you want...

What are you actually trying to do? There may be a simpler way.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[galorin]

I have a designs table that tracks a user-created FM Reference (Architectural design reference, varchar(255)) and a table that tracks the hours worked on a particular design, so there isn't a direct 1 to 1 relationship, as more than one person can work on the same drawing. Before I got to it, the relationship was on the design reference.

I have automated the FM Reference creation and it is no longer stored in the DB, but can be programatically created. Each design gets a design_ID now, auto-incremented. The legacy data in the Designs table now has a design_ID, and new data going into the Hours Worked table is connected to the designs table via a design_ID field.

What I want to do is to give every line of legacy data in the Hours Worked table a design_id table that matches it's entry in the Designs table.

 

[stevexff]

On the face of it, your code should work. You select all the joined rows on FM reference = design_production_no and then use the result to update them. Are you sure all the rows on hoursworked match something on HouseType? Does

SELECT COUNT(*) FROM hoursworked
   WHERE design_production_no NOT IN
   (SELECT DISTINCT 'FM reference' FROM HouseType);

give you about 1100 rows?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[galorin]

Will get back to you on that, made a few tweaks and truncated all my tables so I can re-import data and see what happens, as well as see how log the process takes.

 

[galorin]

On my new data, that query returns 1052 rows, which is the number of rows, give or take, that I was getting before. Maye ther are single character difference.

When I reverse the query, I get 2137 rows, which is to be expected, since quite a few designs won't get loggable work done on them.