MySQL: Creating a new table with information from a query

mysql sql insert sql-update
Saikios picture Saikios · Jul 6, 2011 · Viewed 89.7k times · Source

In MySQL, I would like to create a new table with all the information in this query:

select * into consultaa2 from SELECT
 CONCAT(    'UPDATE customers SET
 customers_default_address_id= ',    
 (SELECT a.address_book_id FROM
 address_book a where
 c.customers_id=a.customers_id order by
 address_book_id desc limit 1),    '
 WHERE customers_id = ', customers_id,
 ';') AS sql_statement FROM customers c
 where c.customers_id > 3894;

The query is too long for the browser to show the concat and I need this to make this updates.

Answer

Daniel Hilgarth picture Daniel Hilgarth · Jul 6, 2011

You can do it like this:

CREATE TABLE tablename SELECT * FROM othertable;

tablename is the name of the new table you want to create, SELECT * FROM othertable is the query that returns the data the table should be created from.

Related questions

How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More
MySQL Error 1264: out of range value for column

As I SET cust_fax in a table in MySQL like this: cust_fax integer(10) NOT NULL, and then I insert value like this: INSERT INTO database values ('3172978990'); but then it say `error 1264` out of value for column …

Read More
How to do a batch insert in MySQL

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there …

Read More