copying data from one table to another

vocoder

I have 2 tables

table1 has fields year,num,upyear,upmonth,upday

table2 has many fields (i won't list them all) and also includes the fields that table1 has

i have created this php script that should update table2 from the data that is in table1 according to year and num......however it fails due to execution time (taking too long). So my guess is that there is a more efficent way to go about this....any help would be greatly appreciated. here's an example

$MyQuery = "SELECT year,num,upyear,upmonth,update FROM beta_add ORDER BY year ASC";

$MyQueryResult = mysql_query($MyQuery, $linkID);





for ($y = 1; $row = mysql_fetch_assoc($MyQueryResult); ++$y){  

    $year = mysql_escape_string($row['year']);
    $num = mysql_escape_string($row['num']);	
    $start_year = mysql_escape_string($row['upyear']); 
    $start_month = mysql_escape_string($row['upmonth']); 
    $start_day = mysql_escape_string($row['upday']); 



$insertme = "UPDATE entry_country SET upyear = '$upyear', upmonth = '$upmonth', upday = '$upday' WHERE year = '$year' AND num = '$num'";   

  	  $MyResult = mysql_query($insertme, $linkID);

barand

A single query will do it

$sql = " INSERT INTO entry_country (year,num,upyear,upmonth,upday ) 
SELECT year,num,upyear,upmonth,upday 
FROM beta_add ";

$result = mysql_query ($sql);

hth

vocoder

but will that just create new entries? I need to update certain dates

for example

table1
2002,203,2003,12,20, maryland, 50000
2002,150,2003,4,15,utah, 400

table2
2002,203,2001,3,14,maryland, 50000
2002,150,2001,1,30, utah, 400

so what I need to do is update the 2001,3,14 in table 2 to the corresponding field in table1 (in this example, it would be updating to 2003,12,20)

barand

Sorry, when I saw the caption I assumed you were transferring records from one table to another.

I've experimented with updating using joins - no go. Guess you have to it as you are now.

vocoder

yes, what I had to do was to run php command line to update it (it took 45 minutes......ugh!) but that wasn't really a problem since I am only doing this once (14,000 records). If I didn't run php from command line, but from the web, I would get cgi timeout errors. Also had to set php's timeout to unlimited.

BlueMeteor

use temp-table?
create one temp-table as table2
->
insert into table3
(select fields-list from table1,table2 )
->
delete from table2
->
insert into table2
select * from table3

It's maybe more efficent than update one by one ....but mysql not support Transactions....you had better lock table first..

barand

Idea has merit, but youd have to use either a union or 2 queries to write to temp as there may be unmatched records that u dont want to lose.