CODEWITHSUNDEEP
mysqlsql
Kushan
Kushan

Reputation: 10695

mysql copy one row data into multiple rows

I have a table such as:

PK FK Value1  value2 value3
1   1    3      2      5
2   2    5      3      6
4   1    9      null   5

I want a query to copy all rows into resulting table, except those that have a null value.

Resulting in:

PK(above table) value(PK) 
1                  3    
1                  2    
1                  5
2                  5
2                  3
2                  6
4                  9 
4                  5

Upvotes: 0

Views: 1452

Answers (2)

paxdiablo
paxdiablo

Reputation: 881503

One way is to do it as three inserts, controlled by a transaction if you need atomicity on the operation:

begin
insert into newtable (pk, value) select pk, value1 from oldtable
    where value1 is not null
insert into newtable (pk, value) select pk, value2 from oldtable
    where value2 is not null
insert into newtable (pk, value) select pk, value3 from oldtable
    where value3 is not null
commit

That's of course assuming your primary key on the new table crosses both columns (I suspect it is since your second column has pk as well). If it's just on pk, no solution is going to work since you'll have a primary key constraint violation.

You can also do it as a union:

insert into newtable (pk, value)
    select pk, value1 from oldtable where value1 is not null
    union select pk, value2 from oldtable where value2 is not null
    union select pk, value3 from oldtable where value3 is not null

Upvotes: 2

Ranu Jain
Ranu Jain

Reputation: 577

You can wirte below queries to achive this

INSERT INTO TABLE2 (SELECT ID,VALUE1 FROM table1);
INSERT INTO TABLE2 (SELECT ID,VALUE2 FROM table1);
INSERT INTO TABLE2 (SELECT ID,VALUE3 from table1);

If you need Single query then you can use

INSERT INTO TABLE2 (SELECT ID,COLUMN1 FROM table1 UNION SELECT ID,COLUMN2 FROM table1 UNION SELECT ID,COLUMN3 from table1)

Upvotes: 1

Related Questions