CODEWITHSUNDEEP
mysql
DinhNgocHien
DinhNgocHien

Reputation: 717

Insert data from a table to another one in MySQL with where condition

I tried this command to insert all records from tblDoXang into tblDoXang1 which satisfy where condition:

insert into gtse.tblDoXang1
SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
 -- where accountID = 'anhtuaniphone' and deviceID = '14C-10152'
-- and from_unixtime(thoiGian) between '2014-11-01 00:00:02' and '2014-11-17 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

It showed me the message: 7432 row(s) affected. After that, I check the data in new table(tblDoXang1) with this query:

SELECT from_unixtime(thoiGian), nhienLieu
FROM `gtse`.`tblDoXang1`

and then I saw: 7432 row(s) returned. I also checked where condition with the old table(tblDoXang):

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
 -- where accountID = 'anhtuaniphone' and deviceID = '14C-10152'
-- and from_unixtime(thoiGian) between '2014-11-01 00:00:02' and '2014-11-17 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

It returned 7432 rows too. However, when I test with a specific value, I received two different results(I expected they were the same). This query returned two records(I select from the new table):

select from_unixtime(thoiGian), nhienLieu
from gtse.tblDoXang1
where
(from_unixtime(thoiGian) between '2014-10-01 00:00:02' and '2014-11-18 23:59:59')
and accountID = 'vinhnghia' 
and deviceID = '14C-00263'

while this threw 5 records(with the same condition)(I select from the old table with the condition I used to insert in the first query above):

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
        where accountID = 'vingnghia' and deviceID = '14C-00263'
       and from_unixtime(thoiGian) between '2014-10-01 00:00:02' and '2014-11-18 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

I confused of this result and I didn't know exactly where was I wrong, so can someone give me some suggestion? Thanks in advance.

Upvotes: 0

Views: 76

Answers (2)

dailammoc
dailammoc

Reputation: 144

Your idea was not good. Please imagine that you have ten accounts in total. With this query:

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
 -- where accountID = 'anhtuaniphone' and deviceID = '14C-10152'
-- and from_unixtime(thoiGian) between '2014-11-01 00:00:02' and '2014-11-17 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

you will only receive the max value(of nhienLieu) in each one hour of ten accounts not of one account(which you expected). So it is the reason why the result was difference between two cases: when you specified specifically accountID,deviceID and thoiGian and when you selected all in your table. I think that this query will satisfy you goal:

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
 -- where accountID = 'anhtuaniphone' and deviceID = '14C-10152'
-- and from_unixtime(thoiGian) between '2014-11-01 00:00:02' and '2014-11-17 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)),
        accountID,deviceID);

Pls try it and show me any problems if you get.

Upvotes: 1

DinhNgocHien
DinhNgocHien

Reputation: 717

I have found my mistake. In this query:

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
 -- where accountID = 'anhtuaniphone' and deviceID = '14C-10152'
-- and from_unixtime(thoiGian) between '2014-11-01 00:00:02' and '2014-11-17 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

I didn't specify time, accountID and deviceID. It was different from:

SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
        where accountID = 'vingnghia' and deviceID = '14C-00263'
       and from_unixtime(thoiGian) between '2014-10-01 00:00:02' and '2014-11-18 23:59:59'
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)));

Where I had specific where condition. So the query:

select from_unixtime(thoiGian), nhienLieu
from gtse.tblDoXang1
where
(from_unixtime(thoiGian) between '2014-10-01 00:00:02' and '2014-11-18 23:59:59')
and accountID = 'vinhnghia' 
and deviceID = '14C-00263'

correspond to this:

SELECT from_unixtime(thoiGian), nhienLieu
FROM (
SELECT *
FROM `gtse`.`tblDoXang`
where concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), nhienLieu) in
    (select
        concat(date(from_unixtime(thoiGian)), hour(from_unixtime(thoiGian)), max(nhienLieu))
        from gtse.tblDoXang
        group by
        date(from_unixtime(thoiGian)),
        hour(from_unixtime(thoiGian)))
) as t
where accountID = 'vinhnghia' 
and deviceID = '14C-00263'
and from_unixtime(thoiGian) between '2014-10-01 00:00:00' and '2014-11-18 23:59:59'
order by thoiGian asc;

That is the reason why I got the different result from two query above.

Upvotes: 2

Related Questions