3

I've done this before, but I don't remember how. I need to select all rows with unique values from several columns in one table, and insert them into one column in a different table. For the destination table they need to be on separate rows in the destination column.

Here's an example:

                        Source Table
+-----+------------+------------+------------+------------+
|  id |  column1   |  column2   |  column3   |  column4   |
+-----+------------+------------+------------+------------+
|  1  |   value1   |   value1   |   value1   |   value1   |
+-----+------------+------------+------------+------------+
|  2  |   value2   |   value3   |   value4   |   value5   |
+-----+------------+------------+------------+------------+

 Destination Table
+-----+-----------+
|  id |  column1  |
+-----+-----------+
|  1  |   value1  |
+-----+-----------+
|  2  |   value2  |
+-----+-----------+
|  3  |   value3  |
+-----+-----------+
|  4  |   value4  |
+-----+-----------+
|  5  |   value5  |
+-----+-----------+

I tried this query but of course it didn't work because the columns aren't equal.

INSERT INTO table2 (column1)
SELECT DISTINCT column1, column2, column3, column4
FROM table1
  • I decided to just do one column at a time and the query worked, would still like an answer on doing multiple columns though. – EternalHour Feb 26 '15 at 12:12
  • Friend, there's something called PIVOT. I'm learning how to use it yet, but you can try it. I see programmers using this here where i work. – Racer SQL Feb 26 '15 at 12:28
  • @RafaelPiccinelli MySQL does not have UNPIVOT which is what you are thinking of. – Mark Sinkinson Feb 26 '15 at 13:55
2

Try with union

insert into table2(column1)
select column1 from table1
union 
select column2 from table1
union
select column3 from table1
-1

Try this...

SELECT col1,
  col2
FROM
  (SELECT rownum X,col_table1 FROM table1
  ) T1
INNER JOIN
  (SELECT rownum Y, col_table2 FROM table2
  ) T2
ON T1.X=T2.Y;
  • Do you have any sample data with output from this query ??? – RolandoMySQLDBA Aug 20 '15 at 16:26
  • 2
    This probably solves some problem but not the one in this question. – Andriy M Aug 20 '15 at 16:26
  • How does this do the insert ? – Rohit Gupta Feb 2 '16 at 3:28

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.