Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query?

For example:

create table `user_mv` select `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang` 

This query creates a table that contains firstname, lastname, lang, username, group_name columns. I want it to also have an id column that is an autoincrement primary key.

Is there any way to do this by changing this query? I know I can do that by altering table after executing this query, but if there is any way to do this directly in the create table statement, I'd like to know how to do that.

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted
CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want. The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.