CODEWITHSUNDEEP
mysqlsql
thanks_in_advance
thanks_in_advance

Reputation: 2743

copy some fields (structure only) into an existing table from another table

I'm using a recent version of MariaDB on a recent version of XAMPP. PHPmyadmin is available.

Let's say I carefully created a grand table with a huge number of fields. Let's call this grand_table. I chose useful field-names, set up appropriate data types for different types of data.

I want to use grand_table as source of ready-made fields for adding fields to future tables. (E.g., if I need to add an email field to an existing table, I don't want to think about datatypes etc., I just want to copy the email field from grand_table. This way I can reduce having to think about it, and also reduce fat-finger typing errors).

Let's say I have another table with a few fields in it. Let's call this fresh_table.

I want to copy only the structure (i.e. the field-names, datatypes etc.) of 20 fields from grand_table to fresh_table. I don't want to copy any data (there actually isn't any data in grand_table).

The 2 tables do not have any common fields among them. So there's no common id field (it shouldn't matter anyway I think because I'm not concerned with importing data here).

I know it's possible to add fields to an existing table using:

ALTER TABLE `fresh_table` ADD field1 VARCHAR(60), 
field2 VARCHAR(60), field2 VARCHAR(60), 
etc... ;

I also know it's possible to create a new table using fields from an existing table using:

CREATE TABLE animals2 AS 
SELECT animal                -- only the columns you want
FROM animals 
WHERE FALSE;                 -- and no data

My question is: is it possible to combine ALTER and SELECT .. FROM to add fields (structure only, no data) to an existing table from another existing table? And if not, is there some other way to achieve the same objective?

Upvotes: 1

Views: 298

Answers (2)

zedfoxus
zedfoxus

Reputation: 37039

Alter + select ... from will not work for an existing table. Workaround would be something like this:

create table large_table (id int, firstname varchar(20), lastname varchar(20), age int);
create table fresh_table (field1 int, field2 int);
create table new_table as select id, firstname, field1, field2 from large_table, fresh_table where 1=0;
select * from new_table;

That will give you columns from fresh_table and large_table. You can then drop fresh_table and rename new_table to fresh_table.

Upvotes: 1

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

this is impossible cause the 2 table don't have any condition.If they have, you can just do this:

    CREATE TABLE animals2 AS 
      SELECT animal                -- only the columns you want
        FROM animals anm
        join animals1 anm1
        on anm.animal=anm1.animal
      WHERE FALSE;

Upvotes: 1

Related Questions