[zoliky]

I have two mySQL table with same coluumns.
The first table called: "files_temp" and second table called: "files"

I want to move data from "files_temp" to "files" and remove the moved data from "files_temp".

I do this with following mySQL querys:

PHP Code:

$msgfiles = mysql_query("SELECT msg_id, userid, name, newname, size, type FROM files_temp WHERE msg_id='".$msgid."'") or die(mysql_error());

        

while ($row = mysql_fetch_array($msgfiles)) 

{

    mysql_query("INSERT INTO files (id, msg_id, userid, name, newname, size, type) VALUES (0, '".$row['msg_id']."', '".$row['userid']."', '".$row['name']."', '".$row['newname']."', '".$row['size']."', '".$row['type']."')") or die(mysql_error());

} 

Exist a better way to do this, to solve this problem with a single mySQL query?

Thanks !

[websmart]

There is more proper way to do that. I had searched various options to
do that and finally settled on the following method:

PHP Code:

$qry1=mysql_query("INSERT INTO `files` SELECT * FROM `files_temp`");

$qry2=mysql_query("TRUNCATE TABLE `files_temp`"); 

First query will copy records from files_temp into table files
and then it erases all records from files_temp. This solution
works perfectly for my tables.


enjoy!

[UnrealEd]

if you have phpmyadmin, simply rename the first table to the second table:

this function can be found in the "Operations" tab when you select a table in your database. Make sure you don't have a table called 'files' otherwie it wont rename

Greetz,
UnrealEd

[End User]

Assuming you had columns named 'title', 'hits', and 'userid', you could do this:

Code:

INSERT  INTO newtable 
SELECT title, hits, userid 
FROM oldtable WHERE title <>  '' 
ORDER  BY title;

No need for temp tables with this method and it's also very fast.