CODEWITHSUNDEEP
mysql
jamjam
jamjam

Reputation: 23

Update one table from another table while using count?

I have stats table with fields id(int) and timestamp(timestamp).

SELECT id FROM stats  GROUP BY id ORDER BY count(id) DESC LIMIT 10;

Gives me the top 10 id's.

I want to put these id's into another table 'popular'

Popular has fields id(int), position(int) and timestamp(timestamp).

To summarize I want to create a query that will update the popular table with the "top 10" id's found in the stats table.

Upvotes: 0

Views: 580

Answers (1)

Linus Kleen
Linus Kleen

Reputation: 34632

INSERT INTO `popular` (`id`,`position`,`timestamp`)
(
   SELECT `id`,COUNT(`id`),NOW() 
   FROM `stats` GROUP BY `id` 
   ORDER BY COUNT(`id`) DESC LIMIT 10
)

It's not quite an update, though. This'll put the new "top 10" entries from stats into popular, yet retain the "old top 10". A TRUNCATE on popular might be necessary before.

UPDATE: TRUNCATE TABLE will empty a given table. It's almost identical to DELETE FROM but it also resets any primary keys that are auto_increment.

Upvotes: 1

Related Questions