2

I'm using MySQL and I have this kind of table:

table: historic_v1 id | student_id | level1_start_date | level1_score | level2_start_date | level2_score 1 | 2345 | 2016-02-10 | 7.5 | 2016-04-15 | 5 2 | 5634 | 2016-03-05 | NULL | NULL | NULL 3 | 4885 | 2015-12-02 | 6 | 2016-01-05 | NULL

I want to do an Insert in table historic_v2 with a Select in historic_v1, but I only want to have a row in historic_v2 if the value of the selected field in historic_v1 is not NULL.

I mean, the historic_v2 table needs to be like this:

table: historic_v2 id | student_id | level | key | date | score 1 | 2345 | 1 | start date | 2016-02-10 | NULL 2 | 2345 | 1 | score | NULL | 7.5 3 | 2345 | 2 | start date | 2016-04-15 | NULL 4 | 2345 | 2 | score | NULL | 5 5 | 5634 | 1 | start date | 2016-03-05 | NULL 6 | 4885 | 1 | start_date | 2015-12-02 | NULL 7 | 4885 | 1 | score | NULL | 6 8 | 4885 | 2 | start_date | 2016-01-05 | NULL

In this example, the student 2345 will have 4 rows in the historic_v2 table, the student 5634 only 1 row and the student 4885 3 rows.

How can I do this Insert with Select?

P.S.: I know that this structure is not nice, but it's just a small example of what I really need to do, and the solution of this problem will help me a lot.

Thanks in advance!

Improve this question
4
  • You could try to use an event or trigger Commented Jun 28, 2016 at 14:45
  • you want insert (more or less) a row in historic_v2 for each column in historic_v1 ? Commented Jun 28, 2016 at 14:54
  • What is the logic behind how many rows have each student? Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. Commented Jun 28, 2016 at 15:00
  • I don't get how difficult to understand is my question for you. In my second paragraph I said that I want to insert into historic_v2 not NULL fields from historic_v1, so the logic behind "how many rows have each student" is the number of not NULL fields in historic_v1 table the user has. Commented Jun 28, 2016 at 15:52

2 Answers 2

Reset to default
2

You can use a select union 

insert into historic_v2 (student_id, level, key , date , score) select student_id, 1, 'start date', level1_start_date, null from historic_v2 where level1_start_date is not null union select student_id, 1, 'score', null, level1_score from historic_v2 where level1_score is not null union select student_id, 2, 'start date', level2_start_date, null from historic_v2 where level2_start_date is not null union select student_id, 2, 'score', null, level2_score from historic_v2 where level2_score is not null
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Similar thread: INSERT with SELECT

You can work off something like this..

INSERT INTO historic_v2 (...) SELECT ... FROM historic_v1 WHERE ...
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.