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
3
  • I decided to just do one column at a time and the query worked, would still like an answer on doing multiple columns though. Commented Feb 26, 2015 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
    Commented Feb 26, 2015 at 12:28
  • @RafaelPiccinelli MySQL does not have UNPIVOT which is what you are thinking of. Commented Feb 26, 2015 at 13:55

2 Answers 2

2

Try with union

insert into table2(column1)
select column1 from table1
union 
select column2 from table1
union
select column3 from table1
0
-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;
3
  • Do you have any sample data with output from this query ??? Commented Aug 20, 2015 at 16:26
  • 2
    This probably solves some problem but not the one in this question.
    – Andriy M
    Commented Aug 20, 2015 at 16:26
  • How does this do the insert ? Commented Feb 2, 2016 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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