SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Making an INSERT using a SELECT

    The following query returns about 3000 rows with the date and number of people who enrolled each day:

    select date(registerDate) as date, count(*) as total from users group by date limit 5000;

    I currently use another query that uses UNIONS to count totals from several different tables. Many of these tables have become quite large, some having more than one million rows, which is slowing things down. So my idea is to create a new statistics table. I will create a cron job that runs at 1AM and it will count the number of new users, searches, favorites from the pervious day and insert them into the statistics table.

    This will work going forward, but I need a way to take the data from the past and insert it into this new table. Is it possible to take my query above and combine it with an INSERT query? Does this make sense?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I made some progress. Tinkering around I came up with this query that did what I wanted:

    INSERT INTO statistics (date, users)
    (select date(registerDate) as date, count(*) as total from users group by date);

    As I was about to change the query and run it again for the searches table I figured I would run into trouble because I might end up with two rows for '2008-02-01', as an example. How can I tweak this query so the insert will add the total to the searches column if that date already exists? And if that particular date doesn't exist it will insert a new row altogether?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    How can I tweak this query so the insert will add the total to the searches column if that date already exists? And if that particular date doesn't exist it will insert a new row altogether?
    use the ON DUPLICATE KEY UPDATE option of the INSERT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I did some further digging around and came up with the following that works. Thanks for pointing me in the right direction:

    INSERT INTO statistics ( date, logins )
    select s.tempDate, ct
    FROM
    ( SELECT t.tempDate, count(*) as ct
    FROM logins t
    GROUP BY t.tempDate
    ) as s
    ON DUPLICATE KEY UPDATE statistics.logins = ct;
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •