mySQL insert column with data from one table to another

Question

I need to create a new table (table 2) with the data from an existing column of table 1:

Table 1:

id1 | item | type

=============

1 | item 1 | type B

2 | item 2 | type A

3 | item 3 | type A

4 | item 4 | type B

5 | item 5 | type C

Table 2:

id2 | type | count

===================

1 | type B | 2

2 | type A | 2

3 | type C | 1

id1 is not the same as id2

I've searched for answers and tried using: SET, UPDATE, INSERT JOIN with AS and FROM like some answers suggested but none of them works for me. How do I do it?

I used these keywords for my search: mysql create table with existing column, copy column from another table, insert column from another table, update column data from another table column...

You can tackle it in two steps - first write a SELECT statement on the first table that gets the results you want. Then use that SELECT to create the new table. — Jerry
– Jerry, Commented Oct 25, 2017 at 0:36
thanks @Jerry I can group all the type by SELECT type FROM table1 GROUP BY type; but then I don't know how to insert that selection to table 2. I tried: INSERT INTO table2 SELECT type FROM table1 GROUP BY type; but it doesn't work. — Viet
– Viet, Commented Oct 25, 2017 at 0:47
It's even easier than you think! dev.mysql.com/doc/refman/5.7/en/create-table-select.html - you can just use the select statement to define your table. — Jerry
– Jerry, Commented Oct 25, 2017 at 0:52
Of course if the data in table 1 changes, your table 2 will be out of date. If you have to keep the second table in sync with the first, consider using a VIEW instead. — Jerry
– Jerry, Commented Oct 25, 2017 at 0:54

emjr · Accepted Answer · 2017-10-25 01:04:16Z

1

Hi just use a view table

https://www.w3schools.com/sql/sql_view.asp

hope this helps :)

answered Oct 25, 2017 at 1:04

emjr

2321 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Viet Over a year ago

Thank you. I've been able to create a new VIEW but the only way to add columns into it is by adding a single column each time using ALTER TABLE ADD. Is there away I can create the new table using the VIEW column from the old table, plus several new columns?

Viet Over a year ago

Something like: CREATE TABLE new_table ( id INT(11) AUTO_INCREMENT NOT NULL, type CREATE VIEW type AS SELECT old_table.type FROM old_table GROUP BY type, another column ... , another column ...., ... );

Viet Over a year ago

Actually, I cannot even use ALTER TABLE to add column: error 1064.

emjr Over a year ago

is your several column data is coming also from another table ?

Collectives™ on Stack Overflow

mySQL insert column with data from one table to another

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related