I have 20 tables with the same structure and they have 3 compound
indexes each. I need to change each of the indexes so the date field is
in descending order.

Is there an easy way to do this with SQL? I thought there should be some
way to do this with the Alter Table sql command but I don't see how to
modify an existing index? Since there are 3 indexes that have to be
modified per table, can it be done in one SQL statement so the table
doesn't have to be rebuilt 3 times?

TIA
Dave

Dave,

I don't know about SQL but here is some code using the "AlterTable"
DBISAM method. I used this in a conversion application I had to write
(note the compound index just before the AlterTable):

//Add the Awards Earned new fields and indicies
dmConvert.tblAwardsEarned.FieldDefs.Update;
dmConvert.tblAwardsEarned.IndexDefs.Update;
dmConvert.tblAwardsEarned.FieldDefs.Insert(4,6,'NewEmployeeID',ftInteger
,0,False);
dmConvert.tblAwardsEarned.FieldDefs.Insert(5,7,'FirstName',ftString,25,F
alse);
dmConvert.tblAwardsEarned.FieldDefs.Insert(6,8,'LastName',ftString,25,Fa
lse);
dmConvert.tblAwardsEarned.IndexDefs.Add('NewEmployeeID','NewEmployeeID',
[]);
dmConvert.tblAwardsEarned.IndexDefs.Add('NameDateAward','LastName;FirstN
ame;Date_Received',[ixCaseInsensitive],'',icFull);
dmConvert.tblAwardsEarned.AlterTable;

With this conversion application I simply added the new indices and
didn't worry about deleting any old ones or "altering one" in
particular. I didn't have to sort in a descending order and I'm not
100% sure on how to do that. Maybe someone else can chime in with that
portion. I used DBISAM v4.x with the above code.

HTH...

--
Regards,
Jan Ferguson


Dave Harrison wrote:

<<I have 20 tables with the same structure and they have 3 compound
<<indexes each. I need to change each of the indexes so the date field
<<is in descending order.
<<
<<Is there an easy way to do this with SQL? I thought there should be
<<some way to do this with the Alter Table sql command but I don't see
<<how to modify an existing index? Since there are 3 indexes that have
<<to be modified per table, can it be done in one SQL statement so the
<<table doesn't have to be rebuilt 3 times?

Dave


Its a doddle,can be done in a script BUT it will need several passes.

eg

DROP INDEX IF EXISTS "Companies".Parent;
CREATE INDEX IF NOT EXISTS "JUNK" ON "Companies" (_Company);

Roy Lambert

Dave,

<< Is there an easy way to do this with SQL? I thought there should be some
way to do this with the Alter Table sql command but I don't see how to
modify an existing index? >>

You can't alter an existing index - you have to drop it and re-create it
using DROP INDEX and CREATE INDEX.

<< Since there are 3 indexes that have to be modified per table, can it be
done in one SQL statement so the table doesn't have to be rebuilt 3 times?
>>

No, it must be done in 3 separate passes.  Also, the table isn't being
rebuilt each time - the only thing that is going on is that an index is
being added to the .idx file.  IOW, there is no difference between the two
other than the fact that the table is being read an additional 2 times.
However, the table reading times for an index build are trivial due to
read-ahead and exclusive buffering.

--
Tim Young
Elevate Software
www.elevatesoft.com