mysql tips - INSERT ... SELECT..., a complex usage with "SELECT" a join table

Mysql can be as powerful as possible to process the data in all your tables, you just need to be patient to figure it out the way to combine all the functions/cmds together. Recently, I have a chance to experience this.

This is base on the MYSQL cmd INSERT ... SELECT ... ON DUPLICATE KEY UPDATE usage, and it is possible that the SELECT part can be as complex as possible.

INSERT t3name (DateTime, t3c1, t3c2, t3c3, t3data)
SELECT CONCAT(date(t1.Date),' ',hour(t1.Date),':00:00') AS DateTime, t1.c1, t1.c2, t1.c3,                     count(t1.c4) AS t3data  FROM table2name t2 
      INNER JOIN table1name t1 on t2.c2=t1.c3 and t2.c3=t1.4 
      WHERE t1.Date like "2013-12-10 %" and t2.Date like "2013-12-10 %" 
      GROUP BY date(t1.Date), hour(t1.Date), t1.c2, t1.c3, t1.c4 
      ORDER BY t1.c2 
ON DUPLICATE KEY UPDATE t3name.t3data=VALUES(t3data);