0

I need to create a new table with all columns from other 4 tables.

My idea for creating the table would be to use the structure:

CREATE TABLE IF NOT EXISTS table_name AS SELECT ~select from other tables~

So far, so good. But I can't find a proper way to do the joins without the foreign key / primary key columns in the table repeating.

Is there a way to use something as distinct, but for columns, no for values?

I know I could do a simpler way, which would be to select exactly the fields I need (like select column.field1, column2.field2), but the 4 tables I need to use to create the new one, have several fields, would be a long code, I don't know if it would be the best way, I wanted something more optimized.

I was also wondering if using this select table structure as per the example would there be any way to set a primary key on creation?

I just tried typing the primary key (column) command after the end of select in various ways (as it would be if I was creating the table by normally setting the fields) and was unsuccessful. Creating the table with AS SELECT, can I only define primary and foreign keys with analter table after creation?

  • Let's see SHOW CREATE TABLE. PRIMARY KEY and FOREIGN KEY (or INDEX) are important for performance, but not a requirement for JOINing. – Rick James Dec 16 '19 at 16:37
  • @RickJamesOkay, maybe my explanation was not clear, but referring to the keys, I was wondering how I can dynamically insert them together with the create table structure CREATE TABLE IF NOT EXISTS table_name AS SELECT ~ select from other tables ~ . I couldn't create the keys in the same table creation query in this case, just inserting the keys after the table creation. – Vinicius Gabriel Dec 16 '19 at 17:10
1

A sample:

USE test

-- Create source tables ...
CREATE TABLE table_1 (id INT, field_1 INT);
CREATE TABLE table_2 (id INT, field_2 INT);
CREATE TABLE table_3 (field_1 INT, field_3 INT);
CREATE TABLE table_4 (field_1 INT, field_4 INT);

-- ... and fill some sample data
INSERT INTO table_1 VALUES (1,1), (2,2);
INSERT INTO table_2 VALUES (1,11), (2,22);
INSERT INTO table_3 VALUES (1,111), (2,222);
INSERT INTO table_4 VALUES (1,1111), (2,2222);
-- Build SQL statement text ...
SELECT CONCAT('CREATE TABLE IF NOT EXISTS new_table AS',
              ' SELECT ', GROUP_CONCAT(fields),
              ' FROM table_1',
              ' NATURAL JOIN table_2',
              ' NATURAL JOIN table_3',
              ' NATURAL JOIN table_4;') INTO @sql
FROM ( SELECT CONCAT(MIN(table_name),'.',column_name) fields
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'test'
       AND table_name IN ('table_1', 'table_2', 'table_3', 'table_4')
       GROUP BY column_name ) fields;

-- ... and execute it ...
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

-- ... then check the result
SELECT * FROM new_table;
    mysql> SELECT * FROM new_table;
    +------+---------+---------+---------+---------+
    | id   | field_1 | field_2 | field_3 | field_4 |
    +------+---------+---------+---------+---------+
    |    1 |       1 |      11 |     111 |    1111 |
    |    2 |       2 |      22 |     222 |    2222 |
    +------+---------+---------+---------+---------+
    2 rows in set (0.00 sec)
-- Drop sample tables
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP TABLE new_table;
| improve this answer | |
  • Hmmmmm, nice ideia. But in my case, all tables have relationship with each other, with Join, would result in 2 columns of the same name right? One being the primary key of one table and its foreign key in another correct table? Still would give error when creating the table due to having two columns with the same name, or I'm wrong – Vinicius Gabriel Dec 16 '19 at 17:07
  • 1
    But in my case, all tables have relationship with each other, with Join, would result in 2 columns of the same name right? Look carefully - in my example column names id and field_1 are not unique over all tableset. The only restriction - if fieldnames in seeparate tables are identical, they ARE joined. – Akina Dec 16 '19 at 17:17
  • i tested and it worked, thanks! Also, would I like with this script already define primary keys or strangers? Or only after creating the table with the alter table? – Vinicius Gabriel Dec 17 '19 at 1:11
  • 1
    @ViniciusGabriel The destination table structure is dynamic fully. After create you may alter it - change fields definitions, add keys, etc. Of course, you may insert structure definition into the query - but in that case you must know the final structure (you cannot define it partially). – Akina Dec 17 '19 at 4:30
1

(This is to address a Comment the OP made.)

When doing CREATE TABLE t SELECT ..., you can specify most (or all?) CREATE things. For example:

CREATE TABLE t (
    aa VARCHAR(123),  -- specify default length
    bb INT DEFAULT '0'  -- specify column not provided by SELECT
    PRIMARY KEY(aa)      -- add an index
) ENGINE=InnoDB
    SELECT 
        aa,    -- converted to VARCHAR(123),
        cc,    -- added to `t` with deduced datatype
    FROM xyz;

You selected 2 columns from xyz; got 3 columns in t, plus an index.

| improve this answer | |
  • I didn't know it worked that way, simple solution and functional. Thanks for contributing. I will keep the correct answer for Akina as it really fit my problem perfectly and in a generic/automated way without me having to declare fields – Vinicius Gabriel Dec 17 '19 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.