Trying to update mysql table with ColdFusion MX 7

I have been trying to update a table in MySQL 5.0 with Coldfusion MX 7. But the query only updates/adds the first records and does not update/add the other 60,000 records.

<cfquery name="count" datasource="voyager">
SELECT 
        PATRON.PATRON_ID, 
        PATRON.HISTORICAL_CHARGES, 
        PATRON.HISTORICAL_BOOKINGS, 
        PATRON.HISTORICAL_DISTRIBUTIONS, 
        PATRON.HISTORICAL_SHORT_LOANS, 
        PATRON.HISTORICAL_CHARGES_UB, 
        PATRON.HISTORICAL_REQUESTS_UB
FROM PATRON
</cfquery>


<cfquery name="update" datasource="patrons">
    <cfoutput>
    REPLACE INTO patrondailyusecounttotals (PATRON_ID, HISTORICAL_CHARGES)
    VALUES ('#count.PATRON_ID#', '#count.HISTORICAL_CHARGES#')
    </cfoutput>
</cfquery>

You'll need to use a loop:

<cfquery name="count" datasource="voyager">
  SELECT 
    PATRON.PATRON_ID, 
    PATRON.HISTORICAL_CHARGES, 
    PATRON.HISTORICAL_BOOKINGS, 
    PATRON.HISTORICAL_DISTRIBUTIONS, 
    PATRON.HISTORICAL_SHORT_LOANS, 
    PATRON.HISTORICAL_CHARGES_UB, 
    PATRON.HISTORICAL_REQUESTS_UB
  FROM 
    PATRON
</cfquery>

<cfloop query="count">
  <cfquery name="update" datasource="patrons">
    REPLACE INTO patrondailyusecounttotals (PATRON_ID, HISTORICAL_CHARGES)
    VALUES (
      <cfqueryparam value="#PATRON_ID#" cfsqltype="CF_SQL_VARCHAR">, 
      <cfqueryparam value="#HISTORICAL_CHARGES#" cfsqltype="CF_SQL_VARCHAR">
    )
  </cfoutput>
</cfloop>

Note that using <cfqueryparam> is the recommended approach here. It will speed up the "inner query" by a lot, since with each loop iteration after the first one only the parameter values are transmitted to the server (instead of transmitting the full SQL code every time).

EDIT

The original version of the answer recommended the following, but obviously that's is not enough in a situation involving separate database servers.

<cfquery name="update" datasource="patrons">
  REPLACE INTO 
     patrondailyusecounttotals (PATRON.PATRON_ID, HISTORICAL_CHARGES)
  SELECT 
     PATRON_ID, HISTORICAL_CHARGES
  FROM 
     PATRON
</cfquery>