MySQL: Creating a new table with information from a query

Question

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 by Daniel Hilgarth is the "correct" answer – Andrew Steitz Jun 04 '19 at 18:36 — Andrew Steitz, Jun 04 '19 at 18:36

score 159 · Answer 1 · answered Jul 06 '11 at 10:59

159

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.

answered Jul 06 '11 at 10:59

Daniel Hilgarth

171,043
40
335
443

1

Is it possible to do something like this, but only create a temporary table? – kevando Apr 01 '16 at 21:42
2

@kevando yes, it is:`CREATE TEMPORARY TABLE tablename AS (SELECT * FROM othertable);` – pymkin May 31 '16 at 04:17
And is it possible to do it just like this but adding an extra field not in the original query? For example, I'd like to add a primary key for the table like `id INT NOT NULL AUTO_INCREMENT PRIMARY KEY`. How can this be done? – Metafaniel Apr 26 '17 at 21:30
You can create a view which is like a temporary table. It will update as your othertable updates. CREATE VIEW tablename as SELECT * FROM othertable; – Cameron Monks Jan 03 '18 at 01:01

score 33 · Accepted Answer · edited Jul 22 '19 at 03:41

33

*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.

edited Jul 22 '19 at 03:41

ashleedawg

20,365
9
72
105

answered Jul 06 '11 at 10:58

Sukumar

3,502
3
26
29

You know I tried the same and didn't work, I just realized that because it was a query the mysql didn't know what type of field to create, really crazy but fixed by creating manually table and field :) – Saikios Jul 06 '11 at 11:01
4

The @daniel-hilgarth answer is actually the right one to Saikios question since it will create the new table based on the selected table sctructure. Saikios query syntax looks a lot like TSQL syntax for doing this. – Murilo Garcia Nov 08 '16 at 15:19

score 14 · Answer 3 · answered Feb 17 '14 at 18:35

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)

MySQL: Creating a new table with information from a query

3 Answers3

Linked