MySQL syntax for create table from select query

Question

I'm trying to create a permanent subset of a table using

create table copydata (select a.* from tabla a
         left join table b 
         on st_distance(a.point, b.point) <= 10
         and b.x is null)

this works but builds the table "copydata" as an InnoDB table. I need this to be an ISAM table so I can build the spatial indices. I've tried

create table copydata (select a.* from tablea a
         left join tableb b 
         on st_distance(a.point, b.point) <= 10
         and b.x is null) ENGINE=MyISAM DEFAULT CHARSET=utf8

but this complains about a syntax error.

What am I missing?

Regards

dds · Accepted Answer · 2014-10-12 01:12:28Z

2

Just change the order of the statements:

create table copydata ENGINE=MyISAM DEFAULT CHARSET=utf8 (select a.* from tablea a
         left join tableb b 
         on st_distance(a.point, b.point) <= 10
         and b.x is null)

answered Oct 12, 2014 at 1:12

dds

2,4151 gold badge34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL syntax for create table from select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related