Hello,
I'm collecting data into a very simple table with one varchar(100) field
("myValue") and one int(10) field ("Qty"). I'm doing this with a simple
"insert into ..... on duplicate key update qty = qty +1".
After the table has reached a count of several 10 million records every
insert takes several seconds - and that's much to slow for my
application.
To increase the speed, I have splitted the table into 50 parts
(table-01, table-02 and so on) and my application is rotating through
this tables. For speed reasons it's ok.
But now I need to export the "top one hundred" of Qty values of each
table into another summary table and, of course, are trying to do the
same "on duplicate key update" thing. I know that it is mathematically
nonsense but it is exact enough for my purposes, because the "top one
hundred" values are very big and the other Qty values are very small...
By now, I'm sending 50 "insert into summarytable select * from table-xx
on duplicate key update commands.
After explaining my details, I would like to know, if there is a
possibility of a command like
insert into summarytable
select * from `table-01` union select * from `table-02` .... union
select * from `table-02`
on duplicate key update Qty ...
I have tried a lot of possibilities, but nearly all caused a sql error
message, the other ones did not do what i want...
Is any one out there who has a good idea of how I can do this in one
command ?
Thanks in advance for any possible help
Detlev
