0

How can I create a column and copy the values and structure from an existing column from another table?

My try:

CREATE COLUMN database.table.column SELECT * FROM database.table2.column
DavidM
  • 307
  • 2
  • 6
  • 22

2 Answers2

2

If you are copy to a new table:

CREATE TABLE newtable 
SELECT columnname
FROM table2

If you want to add a new column to an existing table, you need 

ALTER TABLE existing_table ADD column new_col . . .

And you need to update existing_table based on keys

UPDATE existing_table
SET new_col = (
    SELECT columnname
    FROM table1
    WHERE . . .
)

Depends on how your situation, there is also a lazy way:

SELECT existing_table.*, table1.columnname
FROM existing_table, table1
WHERE . . .
SIDU
  • 2,258
  • 1
  • 12
  • 23
  • @DavidM: The challenge is to add the right row value for each row in the existing table. – wallyk Jan 28 '16 at 00:55
  • 1
    @wallyk Here's an example of how to update a column in a table based on the values of another table. [mysql update column with value from another table](http://stackoverflow.com/a/19346375/21353). So add the new column with the `Alter Table` statement and then create an update query to set the values of the new column. – Adam Porad Jan 28 '16 at 01:00
1

To copy full table structure and values

SELECT * INTO NewTableName FROM ExistingTableName

To copy specific structure and value

SELECT ExistingTableName.Column1,ExistingTableName.Column2 INTO NewTableName FROM ExistingTableName

SAMPLE QUERY

Lemon Kazi
  • 3,308
  • 2
  • 37
  • 67
Azizur Rahman
  • 68
  • 5