0

I have a table named "trans" with this structure

#   Name    Type    Collation   Attributes  Null    Default Extra   Action
 1  user_id int(11)         No  None    Change Change    Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 2  amount  decimal(16,8)   No  None     Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 3  aff int(11)             No  0        Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 4  jackpot int(11)         No  0        Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 5  paidout int(11)         No  0        Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 6  type    int(11)         No  0        Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More
 7  created timestamp       No  CURRENT_TIMESTAMP        Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique   Index Index    Spatial Spatial Show more actions More

This table keep growing millions of rows and every few days a have to collapse the table doing this query:

SELECT user_id,paidout,sum(amount) as amount FROM trans GROUP BY user_id,paidout

then i export the result of this query to my computer in sql format

then i empty the "trans" table and then i import to "trans" the sql file i exported from that query above.

My question is , if there is any way to make this process run automated daily?

VMai
  • 10,156
  • 9
  • 25
  • 34
user38935
  • 5
  • 4
  • 2
    Do you really need to do it with MySQL **and** Microsoft SQL Server or you've slipped a wrong tag? – Álvaro González Jul 05 '14 at 16:12
  • 2
    Yes. Read about crontab and bash scripts. – mus Jul 05 '14 at 16:13
  • 1
    If it's MySQL, you can use the [Event scheduler](https://dev.mysql.com/doc/refman/5.6/en/events.html), if it's MS sql server, use the sql server agent. – VMai Jul 05 '14 at 16:16
  • why don't you simply update the fields instead of adding a complete new row and grouping it after? – chresse Jul 05 '14 at 16:48
  • @chresse i would update if you can suggest me the exact query , but i could not find another way to do this. – user38935 Jul 05 '14 at 17:23
  • `UPDATE trans SET amount = amount + WHERE user_id = ` – chresse Jul 05 '14 at 17:34
  • @chresse #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE user_id = ' at line 1 – user38935 Jul 06 '14 at 14:05
  • @user38935 you have to adjust the command with the values you need. have a look at http://stackoverflow.com/questions/2259155/increment-value-in-mysql-update-query to see how values are incremented by `UPDATE` – chresse Jul 06 '14 at 17:19

0 Answers0