On 26 Sep, 14:26, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Tue, 25 Sep 2007 13:10:43 -0700, "N. Sloane"
>
>
>
>
>
> <n...@invidion.co.uk> wrote:
> >Hi,
>
> >I've been trying to construct a MERGE table in MySQL from about 5000
> >other tables, each of which have a few thousand entries. However, when
> >I create and try and access it, I keep getting the dreaded error:
>
> >ERROR 1064 (42000): You have an error in your SQL syntax; check the
> >manual that corresponds to your MySQL server version for the right
> >syntax to use near ....
>
> >I've read the docs, and am using the same table type (MyISAM), and all
> >the table columns of each of the 5000 tables is exactly the same. Each
> >table has a primary key called 'id', and when I try and create the
> >merge table, I use
>
> >INDEX(id)
>
> >to specify the index, not making it a primary key.
>
> >I'm sure my MySQL syntax is correct;
>
> I'm sure it's not. When you show your syntax I might change my
> mind.
>
> >I'm wondering if there are any
> >additional procedures I need to go through before this. For instance,
> >do I need to re-order or ammend the indexes of the other tables before
> >I merge them?
>
> Not that I'm aware of. Still, that has nothing to do with syntax
> errors.
>
> >I'm just wondering if other people know of any obscure reasons that
> >cause problems when creating merge tables.
> >Many Thanks
> >Regards
> >Neil.
>
> Regards,
> --
> ( Kees
> )
> c[_] A chicken is an egg's way of producing more eggs. (#436)- Hide quoted text -
>
> - Show quoted text -


Syntax below:

Query to create tables (numbered from 1 - 5355)

CREATE TABLE `1` ( `date` date NOT NULL, `symbol` char(20) default
NULL, `open` decimal(10,2) default NULL, `close` decimal(10,2) default
NULL, `adjClose` decimal(10,2) NOT NULL, `high` decimal(10,2) default
NULL, `low` decimal(10,2) default NULL, `volume` decimal(10,2) default
NULL, `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`))

Query to create Merge Table:

CREATE TABLE `shares` ( `date` date NOT NULL, `symbol` char(20)
default NULL, `open` decimal(10,2) default NULL, `close` decimal(10,2)
default NULL, `adjClose` decimal(10,2) NOT NULL, `high` decimal(10,2)
default NULL, `low` decimal(10,2) default NULL, `volume` decimal(10,2)
default NULL, `id` int(11) NOT NULL auto_increment, INDEX(id))
ENGINE=MRG_MyISAM UNION=(`1`, `2`, `3`, ......, `5355`) INSERT
METHOD=LAST


You are proably right in saying that the above syntax is incorrect in
some way that I just can't see.

I noticed that although I do not explicitly declare the main tables as
MyISAM tables, they are created as MyISAM tables, so assumed that that
would not cause a problem.

However, in the merge code, should I change INDEX(id) to KEY(id) ?

Many Thanks
Neil.