How to insert the gathered dates into another table using INSERT SELECT in MySQL?

Question

insert into rfdate (date)
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday')

Above code doesn't work.

Code below gathers the needed date irregardless of where database you are in since it's just date. You can try it too even without using a table with the columns presented.

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

Answer 1

PROPOSED QUERY

insert into rfdate (date)
select selected_date from
(
 select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
 from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4
 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4
 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4
 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4
 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4
 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) A where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

PROOF THAT IT WORKS

mysql> DROP DATABASE jeffvalerio;
Query OK, 1 row affected (0.02 sec)
mysql> CREATE DATABASE jeffvalerio;
Query OK, 1 row affected (0.00 sec)
mysql> USE jeffvalerio
Database changed
mysql> CREATE TABLE rfdate
 -> (
 -> id int not null auto_increment,
 -> date date,
 -> PRIMARY KEY (id)
 -> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into rfdate (date)
 -> select selected_date from
 -> (
 -> select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
 -> from
 -> (select 0 t0 union select 1 union select 2 union select 3 union select 4
 -> union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 -> (select 0 t1 union select 1 union select 2 union select 3 union select 4
 -> union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 -> (select 0 t2 union select 1 union select 2 union select 3 union select 4
 -> union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 -> (select 0 t3 union select 1 union select 2 union select 3 union select 4
 -> union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 -> (select 0 t4 union select 1 union select 2 union select 3 union select 4
 -> union select 5 union select 6 union select 7 union select 8 union select 9) t4
 -> ) A where selected_date between '2015-02-01' and '2015-02-28'
 -> and dayname(selected_date) in ('Monday', 'Tuesday');
Query OK, 8 rows affected (0.09 sec)
Records: 8 Duplicates: 0 Warnings: 0

Here is the table's contents

mysql> select * from rfdate;
+----+------------+
| id | date |
+----+------------+
| 1 | 2015年02月02日 |
| 2 | 2015年02月03日 |
| 3 | 2015年02月09日 |
| 4 | 2015年02月10日 |
| 5 | 2015年02月16日 |
| 6 | 2015年02月17日 |
| 7 | 2015年02月23日 |
| 8 | 2015年02月24日 |
+----+------------+
8 rows in set (0.00 sec)
mysql>

GIVE IT A TRY !!!

• Jeff Valerio: "#1054 - Unknown column 'selected_date' in 'where clause' " That's the output error. :(
• RolandoMySQLDBA: Just fixed it...