将mysql查询的结果插入现有表中

Question

I have a an existing table with timestamps and other values and I wanted to create dummy timestamps for each day in the table

For example

    timestamp           phase_1 phase_2 phase_3
2014-03-04 12:00:00   0       0        0
2014-03-05 02:00:00   0       0        0
2014=03-06 01:00:00   0       0        0
2014-03-07 00:00:00   0       3        1

should result to

timestamp           phase_1 phase_2 phase_3
2014-03-04 00:00:00   0       0        0     --<< new
2014-03-04 12:00:00   0       0        0
2014-03-05 00:00:00   0       0        0     --<< new
2014-03-05 02:00:00   0       0        0
2014-03-06 00:00:00   0       0        0     --<< new
2014-03-06 01:00:00   0       0        0
2014-03-07 00:00:00   0       3        1

The following query works fine

`select * from Table1

right join

(select
        `timestamp`
      , phase_1
      , phase_2
      , phase_3
from Table1

union

select
        date(`timestamp`)
      , 0
      , 1
      , 2
from Table1

order by 
        `timestamp`) `

but I am not able to insert the result into the exisiting database

using the following query

`INSERT into Table1

select * from Table1

right join

(select
        `timestamp`
      , phase_1
      , phase_2
      , phase_3
from Table1

union

select
        date(`timestamp`)
      , 0
      , 1
      , 2
from Table1

order by 
        `timestamp`) `

Which i think is correct but i get a

: Every derived table must have its own alias:  error which i am not sure on how to solve

Here is the fiddle enter link description here

Also, will it be possible to insert dummy timestamp and values for days which are not there at all in the table?

Suppose there are two days between the first and last rows in the table and there are no timestamps for those days in the table, will it be possible to add those two dates into the table.

I want to avoid trigger and procedures if possible as I am not at all familiar with them and since the table is dynamically generated, i think it wont be possible to use them.

Answer 1

You've got to use an alias name for your UNION, but you've got to provide an JOIN condition for your right join too. Probably you want to use:

INSERT INTO Table1
select a.* from Table1    
right join    
(
    select
        `timestamp`
      , phase_1
      , phase_2
      , phase_3
    from Table1

    union

    select
        date(`timestamp`)
      , 0
      , 1
      , 2
    from Table1

    order by 
        `timestamp`
) as a
ON DATE(a.timestamp) = DATE(Table1.timestamp)

Remarks:

What is the difference between Left, Right, Outer and Inner Joins? provides a very good explanation of the different joins.

An outer join needs a join condition, an inner join without a condition is the same as an CROSS JOIN, the cartesian product of both tables (most times one wants to avoid this - but not ever).

If you use a SELECT statement, that works perfectly as SELECT statement alone in a join, then the result of this SELECT statement changes into a derived table and you've got to give this derived table a name so you can use the columns of the derived table.

I hope it will be a little bit more clear now.