CODEWITHSUNDEEP
mysqlphpmyadmin
Michal Sladký
Michal Sladký

Reputation: 23

INSERT INTO SELECT mysql is trying to insert different data from the selected ones

I've been messing around with MySQL lately and I can't resolve one problem.

Having 2 tables.

First one is storing data about online players every 5 minutes, second one is for daily statistics. It's separated, so I don't have to run crazy selects whenever man wants to see min,avg,max online players for last month for example.

This query works, result is a table with dates that haven't been in status_statistika yet.

SELECT date,MIN(playersonline),AVG(playersonline),MAX(playersonline) 
    FROM status_graf 
    GROUP BY date 
    HAVING date NOT IN (
        SELECT date 
        FROM status_statistika 
        GROUP BY date
    )

But when I try this

INSERT INTO status_statistika (
    SELECT date,MIN(playersonline),AVG(playersonline),MAX(playersonline) 
    FROM status_graf 
    GROUP BY date 
    HAVING date NOT IN (
        SELECT date 
        FROM status_statistika 
        GROUP BY date
    )
    );

it does not.

Using phpMyAdmin 4.3.8, MySQL 5.5.41 and it throws error #1062 - Duplicate entry '2015-01-29' for key 'date'.

Ideas?

SOLUTION: Since the dot convention somehow didn't accept it, I had to rename column 'date' in one table to 'day' and then it worked

Upvotes: 2

Views: 534

Answers (2)

gview
gview

Reputation: 15391

Looks like you have unique index on the date column in the status_statistika table.

Upvotes: 0

terary
terary

Reputation: 1108

INSERT INTO status_statistika (
    SELECT DISTINCT date,MIN(playersonline),AVG(playersonline),MAX(playersonline) 
    FROM status_graf 
    GROUP BY date 
    HAVING date NOT IN (
        SELECT date 
        FROM status_statistika 
        GROUP BY date
    )
    );

Notice the 'DISTINCT'

Upvotes: 0

Related Questions