Mysql Question posted in
The official MySQL documentation can be found here.

How to use select in Mysql to expand existing table

Suppose I have a table 1 (4 rows and 3 columns) which is already in a database, I want to expand to table 2 using table 1 and variables (A, B, C, D). Table 2 will contain 8 rows and 5 columns. Can a simple select statement do that?

Table1

Col1 Col2 Col3
R11 R12 R13
R21 R22 R23
R31 R32 R33
R41 R42 R43

Table 2

Col1 Col2 Col3 Col4 Col5
R11 R12 R13 A B
R21 R22 R23 A B
R31 R32 R33 A B
R41 R42 R43 A B
R11 R12 R13 C D
R21 R22 R23 C D
R31 R32 R33 C D
R41 R42 R43 C D
Tags:

2

Answers


  1. 0

    You could use this DDL and SQL to create Table 2 from Table 1:

    -- Create Table 2
CREATE TABLE Table2 (
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50),
    Column4 VARCHAR(50),
    Column5 VARCHAR(50)
);

-- Insert data into Table 2
INSERT INTO Table2 (Column1, Column2, Column3, Column4, Column5)
SELECT
    Column1,
    Column2,
    Column3,
    'A' AS Column4,  -- Add constant value 'A' to Column 4
    'B' AS Column5   -- Add constant value 'B' to Column 5
FROM Table1;

-- Insert data with 'C' and 'D' values into Table 2
INSERT INTO Table2 (Column1, Column2, Column3, Column4, Column5)
SELECT
    Column1,
    Column2,
    Column3,
    'C' AS Column4,  -- Add constant value 'C' to Column 4
    'D' AS Column5   -- Add constant value 'D' to Column 5
FROM Table1;

    If you only want to expand Table 1 to look like your Table 2, you can use this DDL and SQL:

    ALTER TABLE Table1
ADD Column4 VARCHAR(50) DEFAULT 'A',
ADD Column5 VARCHAR(50) DEFAULT 'B';

-- Update values in Column4 and Column5 in Table1 to match Table2
UPDATE Table1
SET Column4 = 'A', Column5 = 'B';

-- Insert additional rows into Table1 to match Table2
INSERT INTO Table1 (Column1, Column2, Column3, Column4, Column5)
SELECT
    Column1,
    Column2,
    Column3,
    'C' AS Column4,
    'D' AS Column5
FROM Table1;
    Login or Signup to reply.
  2. 0 
    SELECT *
FROM table1
CROSS JOIN (
    SELECT 'A' col4, 'B' col5
    UNION ALL
    SELECT 'C', 'D'
    ) additional_table

    https://dbfiddle.uk/o13iC_Cr

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search