MySQL - data changes with subqueries

[joedub]

Hello,

I am trying to construct an sql query that will take each row in 1 table, insert certain columns from this row into another table.

For example i have tables "domains" and "domains_months".

domains:
DomainID
DomainHitsThisMonth
DomainHitsLastMonth

domains_months:
MonthID
MonthDomainID
MonthPeriod
MonthDomainHits

The intention is to go through the rows in domains table, insert a new row into domains_months like:

MonthDomainID = DomainID
MonthDomainPeriod = date( "d-m-Y", time() )
MonthDomainHits = DomainHitsThisMonth

and then with the row from domains we are using update the row so:

DomainHitsLastMonth = DomainHitsThisMonth
DomainHitsThisMonth = 0

Im trying to acheive this with a single SQL statement, rather than hte php equivalent which would look something like:

<? 
 // fetch all domains from domains table
 $domains = mysql_fetch_table( 'domains' );
 
 // foreach domain row returned
 foreach( $domains as $value )
 { 
 // construct array to insert into domains_months table
 $row = array
 (
  'MonthDomainID'  => $value['DomainID'],
  'MonthDomainPeriod' => date( "d-m-Y", time() ),
  'MonthDomainHits' => $value['DomainHitsThisMonth']
  
 );
 $inserted = mysql_insert_row( 'domains_months', $row );
 // now update the row we were working on
 $new_row = array
 (
  'DomainHitsLastMonth' = $value['DomainHitsThisMonth'],
  'DomainHitsThisMonth' = 0
 );
 $updated = mysql_update_row( 'domains', 'DomainID', $value['DomainID'], $new_row );
 }
?>

[arran]

Hi joedub, this will have to be done in 2 statements (although i suppose you could combine these into one stored procedure).
It would be something like:

insert into domains_months select $MonthID, d.DomainID, $MonthDomainPeriod, d.DomainHitsThisMonth from domains d;

update domains set DomainHitsLastMonth = DomainHitsThisMonth, DomainHitsThisMonth = 0;

[coopster]

Agree. "Currently, you cannot update a table and select from the same table in a subquery."

Source (last sentence):
[dev.mysql.com...]