Re: [ISCS-devel] Database bug

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

Alas, mysql 4.x returns an error:

mysql> update all_res_ipranges set containsbestmatch=0 where actualrange
in (select actualrange from all_res_ipranges left join best_match_res_ip
on lowbinary=superlow and highbinary=superhigh where containsbestmatch=1
and superlow is null);
ERROR 1064 (00000): You have an error in your SQL syntax near 'select
actualrange from all_res_ipranges left join best_match_res_ip on
lowbinar' at line 1

Do you or anyone happen to know what the proper subclause syntax is for
MySQL < 5.x? Thanks - John

On Wed, 2007-12-19 at 18:33 -0300, Daniel Medeiros wrote:
> At first, I forgot confirming that you were right about the problem.
> In an UPDATE statement you cannot reference more than one table.
> 
> I'm at work now and the CVS to outside is blocked, but if you don't
> mind I'd to guess your answer. 
> 
> I may confess I am not so used to SQL:2003 (I have worked more with
> SQL99), and I am not sure if it will work in every database you have,
> you'll need to test them. But, even without seeing your database, just
> by your previous comments and commands, I'd guess these commands: 
> 
> update accessors_ip set containsbestmatch=0 
> where actualrange in ( select actualrange from accessors_ip left join
> best_match_acc_ip on lowbinary=superlow and
>          highbinary=superhigh where
>          containsbestmatch=1 and superlow is null); 
> 
> and 
> 
> update all_res_ipranges set containsbestmatch=0 
> where actualrange in ( select actualrange from all_res_ipranges left
> join best_match_res_ip on lowbinary=superlow and
>    
>  highbinary=superhigh where 
>          containsbestmatch=1 and superlow is null);
> 
> 
> 
> 
> On Dec 19, 2007 5:24 PM, John A. Sullivan III
> <jsu...@op...> wrote:
>         That would be great, Daniel.  The ERDs and table definitions
>         are in the
>         CVS under devel-docs/Database.
>         
>         So how is the world going for you? Planning anything special
>         for
>         Christmas? Take care - John
>         
>         
>         On Wed, 2007-12-19 at 15:32 -0300, Daniel Medeiros wrote:
>         > Hey, John,
>         >
>         > I guess I can help you, even being out of the project. Could
>         you send
>         > me the database schema? I'll take a look and answer you
>         ASAP. 
>         >
>         > - Daniel
>         >
>         > On Dec 19, 2007 3:08 PM, John A. Sullivan III <
>         > jsu...@op...> wrote:
>         >         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
>         >         --
<snip>
> 
-- 
John A. Sullivan III
Open Source Development Corporation
+1 207-985-7880
jsu...@op...

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