List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 9:51pm
Subject:CREATE TABLE ... SELECT [was Re: Delete table]
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On Tue, 12 Oct 1999 sinisa@stripped wrote:
>> Michael Farr writes:
>> > Hopefully this will be the last question, then I'll leave yas alone.
>> > 
>> > when I do a 
>> > 
>> > CREATE TABLE Patient2 SELECT p.* FROM Patient p LEFT JOIN
>> > DentalScheme d ON p.patientId = d.patientId WHERE NOT
>> > ISNULL(p.patientId) ;
>> > 
>> > is the new table Patient2 supposed to have all the special
>> > options to it.  Because it seams to miss the fact that patientId is
>> > a primary key (I fixed it already with an "alter table etc")
>> I must say that (this time) Bob is right !! You should have order by
>> or group by clauses to indicate something like that !!

Bob> Sinisa:

Bob> Are you saying that there is a way to induce MySQL to automatically set
Bob> a column as a primary key using the CREATE TABLE ... SELECT ... syntax
Bob> (I assume that's what you mean by "... to indicate something like that"  
Bob> since that's what the original poster is asking for)?  Is there a
Bob> description of how to control this behavior somewhere in the manual
Bob> (couldn't find any)?

Yep.  For example

CREATE TABLE Patient2 (PRIMARY KEY (patientId)) SELECT ...

The idea is that anything between table-name and SELECT is added to
the create statement after all columns that are used in the SELECT.

Bob> Monty:

Bob> If the query as quoted by the original poster is accepted by the parser
Bob> (regardless of whether it's possible to trigger creation of a primary
Bob> key the way the poster wants), the grammar specification for CREATE
Bob> TABLE may need to be modified in section 7.6 of the manual, since the
Bob> way it is currently specified the "(create definition,...)" portion is
Bob> required for all variants.

I shall fix this at once.

Bob> Michael:

Bob> It's always a good idea to change the subject header for a message
Bob> thread when you change the subject.  This makes it easier for someone
Bob> who wants to know the same thing you're asking about to find the answers
Bob> in the list's archives.  Thanks!

Delete tableashley pestkowski11 Oct
  • Re: Delete tableBob Kline11 Oct
  • Re: Delete tableMartin Ramsch11 Oct
Re: Delete tableMichael Farr11 Oct
  • Re: Delete tableBob Kline11 Oct
  • Re: Delete tablesinisa12 Oct
    • CREATE TABLE ... SELECT [was Re: Delete table]Bob Kline12 Oct
      • CREATE TABLE ... SELECT [was Re: Delete table]Michael Widenius12 Oct
        • Re: CREATE TABLE ... SELECT [was Re: Delete table]Bob Kline13 Oct