Thank you for the fast response!!
Well the same person who created that table is the one who created that bash script.
in any case, the field names are a bit confusing i agree, let me make things a bit clearer by this example:
n1,n2,n3,n4,n5,n6,n7 (11-09-2014,1,40,9,22,4,10,31)
stand for :
this check has been made on 11-09-2014
employee with ID 1,
Age 40,
rank #9,
salary range 22,
manages 4 projects,
finished 10 projects this year,
date overdue on current projects 31.
i plan to change the table as you suggested, but given the information above would it still make sense to have just one "idx" field ?
excuse me if this question sound silly...
Peter Brawley Wrote:
-------------------------------------------------------
> If the idiot who designed those tables hasn't been
> fired, he needs to be.
>
> Naming columns ...1, ...2 &c where queries must
> treat those numerical indices as data, requiring
> looping constructs as in your code, is one of the
> worst possible database design mistakes. Your
> ludicrously bad Update performances exacts the
> price. Both tables need to be migrated to a design
> like this ...
>
> n tinyint
> idx tinyint (taking values corresponding to
> indices in n1,n2 &c
>
> And the date column need to become a proper date.
>
> For example ...
>
> drop table if exists newresult;
> create table newresult(date date, n tinyint, idx
> tinyint, primary key (date, n, idx);
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n1, 1,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n2, 2,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n3, 3,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n4, 4,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n5, 5,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n6, 6,
> whenadded from oldresult;
> insert into newresult(date, n, idx, whenadded )
> select str_to_date(date,'%m/%d/%Y'), n7, 7,
> whenadded from oldresult;
>
> (Adjust the str_to_date arg if the varchar date is
> not in US date format.)
>
> And similarly for the other table.
>
> Then you can write simple update queries that will
> execute in reasonable time.