CODEWITHSUNDEEP
mysqlselectinsert
Joris
Joris

Reputation: 544

mysql insert into, select from other table while altering data

I want to compress my mysql records into a new table. My current table looks like this:

time            user        value
1392901726      jo          12
1392901727      jo          5
1392999728      jo          3032
1392901726      kada        1
1392901727      kada        2
1392999728      kada        123

I want to do the compression by dividing the time by 1000, the values need to be accumulated per new time. The above example should become:

time            user        value
1392901         jo          17
1392999         jo          3032
1392901         kada        3
1392999         kada        123

I have already a query that takes data from the input table and stores it into the new table. This query already accumulates the values. I am only not able to convert the old time into the new time so that multiple records collapse into a single record:

INSERT INTO userTest (`time`, `user`, `value`)
SELECT *
FROM   userStats
WHERE `user` =  'jo'
ON DUPLICATE KEY UPDATE
`time`= VALUES(`time`), `user` = VALUES(`user`), `value` = userTest.value + VALUES(value)

How do I change the above query so that the time is divided by 1000 for both the first insert and in the ON DUPLICATE KEY UPDATE part?

Upvotes: 2

Views: 210

Answers (2)

Joris
Joris

Reputation: 544

In the ON DUPLICATE KEY UPDATE we should not divide the time by 1000, so the final answer should be:

INSERT INTO userTest (`time`, `user`, `value`)
SELECT time/1000, user, value
FROM   userStats
WHERE `user` =  'jo'
ON DUPLICATE KEY UPDATE `time`= VALUES(`time`), `user` = VALUES(`user`), `value` =     userTest.value + VALUES(value)

Upvotes: 1

Bernhard Döbler
Bernhard Döbler

Reputation: 2117

Select the fields you want to insert and manipulate them.

INSERT INTO userTest (`time`, `user`, `value`)
SELECT time/1000, user, value
FROM   userStats
WHERE `user` =  'jo'
ON DUPLICATE KEY UPDATE `time`= VALUES(`time`/1000), `user` = VALUES(`user`), `value` = userTest.value + VALUES(value)

Upvotes: 0

Related Questions