MySQL: Creating a new table with information from a query

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

*Note that this method does not create a table (as per OP title). To do that see this answer.*

Inserting into a table with information from a query is of the format

INSERT INTO <TABLE-1> 
SELECT * FROM <TABLE-2>

In your case, it would be

insert into consultaa2 
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;

Just make sure the columns in the table you are inserting into and the columns returned from the select query match.

Method 3

mysql create new table

Example from mysql commandline.

mysql> create table foo(id int, vorta text);
Query OK, 0 rows affected (0.02 sec)

Insert rows

mysql> insert into foo values(1, 'for the hoarde');
Query OK, 1 row affected (0.00 sec)

look what’s in there

mysql> select * from foo;
+------+----------------+
| id   | vorta          |
+------+----------------+
|    1 | for the horde  |
+------+----------------+
1 row in set (0.00 sec)

Create a new table with information from a query

mysql> create table foo2 select * from foo;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Check if the data moved

mysql> select * from foo2;
+------+----------------+
| id   | vorta          |
+------+----------------+
|    1 | for the horde  |
+------+----------------+
1 row in set (0.00 sec)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0