MySQL creates a temporary table with the identifier auto_increment and selects a query

Question

you want to create a temporary table with the auto_increment field plus the field that you want to select from another table.

Here is what I have (doesn't work)

CREATE TEMPORARY TABLE tmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, (SELECT valueName AS valueName FROM sometable WHERE sometable.somevalue='00')); 

they work on their own, but can get the correct syntax to do both

 CREATE TEMPORARY TABLE tmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) CREATE TEMPORARY TABLE tmp AS SELECT valueName AS valueName FROM sometable WHERE sometable.somevalue='00'; 

Answer 1

 CREATE TEMPORARY TABLE tmp ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, valueName varchar(16) // whatever type it should be ); INSERT INTO tmp (valueName) SELECT valueName FROM sometable WHERE ... 

Relevant documents here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

Answer 2

I think you might try to make the first case described here:

http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html

.. which for your example would look like this:

 CREATE TEMPORARY TABLE tmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT valueName AS valueName FROM sometable WHERE sometable.somevalue='00'; 

.., so it can only be paranas in the wrong places that bit you on the first try.