[ISCS-devel] Database bug

[John A. S. I. <jsu...@op...>]

Hello, all.  While writing the edit Resource code we need for a current
project, I came across a bug in the code which may have made some
erroneous database entries however I do not have the SQL knowledge to
know how to write an SQL statement to find and fix the erroneous
entries.  Can someone suggest how we can write a patch for the next
release to repair the database?

The problem is that we erroneously returned a value of true for
containsbestmatch when it should have been false.  To find the errant
rows in the all_res_ipranges table (for Resources) or the accessors_ip
table (for Accessors) we can do:

select actualrange, containsbestmatch from all_res_ipranges left join
best_match_res_ip on lowbinary=superlow and highbinary=superhigh where
containsbestmatch=1 and superlow is null;

and

select actualrange, containsbestmatch from accessors_ip left join
best_match_acc_ip on lowbinary=superlow and highbinary=superhigh where
containsbestmatch=1 and superlow is null;

But how do we find the rows and then fix them in a single SQL statement?
Something like:

update accessors_ip set containsbestmatch=0 left join best_match_acc_ip
on lowbinary=superlow and highbinary=superhigh where containsbestmatch=1
and superlow is null;

does not work since I think one can have only one table in an update
statement.  I'd like to fix this ASAP so any help would be greatly
appreciated.  Thanks - John
-- 
John A. Sullivan III
Open Source Development Corporation
+1 207-985-7880
jsu...@op...

Financially sustainable open source development
http://www.opensourcedevel.com