<?php
$link = mysql_connect("host","user","pass");
mysql_select_db("db_name",$link);
$selectSQL = "SELECT * FROM `sometable` ORDER BY `order_column` DESC LIMIT 1;"; // Remove or change the LIMIT 1 depending on how many rows you would like to move to the other table
$query = mysql_query($selectSQL,$link);
while($row = mysql_fetch_object($query)){
$deleteSQL = "DELETE FROM `sometable` WHERE `identifying_column` = '".($row->identifying_column)."';";
$updateSQL = "INSERT INTO `othertable`(`identifying_column`,`order_column`,`column3`,`column4`) VALUES('".($row->identifying_column)."','".($row->order_column)."','".($row->column3)."','".($row->column4)."');";
mysql_query($deleteSQL,$link);
mysql_query($updateSQL,$link);
}
?>
It can be done in two by using INSERT ... SELECT followed by DELETE, but (as you surmise) not one.
START TRANSACTION;
INSERT INTO [I]target[/I] SELECT * FROM [I]source[/I] ORDER BY [I]col[/I] DESC LIMIT 1;
DELETE FROM [I]source[/I] ORDER BY [I]col[/I] DESC LIMIT 1;
COMMIT;
It is good practice misson to separate subqueries with (), but I think a SELECT INTO statement would be better suited to this.
SELECT *
INTO new_table_name
FROM old_tablename
WHERE [I]condition[/I]
LIMIT 1;
DELETE
FROM [I]source[/I]
WHERE [I]condition[/I]
LIMIT 1;TABLE (
....
) type=innodb;The SELECT in INSERT ... SELECT isn't exactly a subquery, so I didn't bother.
MySQL doesn't support SELECT ... INTO.
Both SELECT ... INTO and INSERT ... SELECT require a search and an insert, and will thus perform similarly.
A ROLLBACK is only sent to discard the changes introduced by a transaction; it shouldn't be needed here. Since the OP never mentioned anything other than SQL, I didn't bother with any other language.
Or even SQL standards and extensions. The INTO clause isn't even standard. To be fair, SQL support in DBMSs is worse than browser support for web standards; it's tricky to the point of being pointless to cover all the whys and wherefores.
You can rollback the transaction, it's just that that isn't why I suggested it. The transaction is to make the queries atomic. You could also lock the tables explicitly, but a transaction should perform better because MySQL can delay locking until it has all the statements in the transaction.
, [html] or [php] tags as appropriate.
If you're generating an SQL query that isn't working, check the generated statement. If you don't have a PHP debugger (e.g. [URL="http://phpandtips.wordpress.com/2009/02/15/eclipse-pdtxdebug/"]Eclipse+PDT+XDebug[/URL]), add some scaffolding to the script to print the statement . You'll see something like:
[quote]INSERT INTOmembersVALUES (‘‘, bob, [email]bob@example.net[/email])
SELECT username, email
FROMusersORDER BY timestamp DESC LIMIT 1[/quote]
As you see, this has numerous problems. There's no space around the table names. You've mixed the [FONT="Courier New"]INSERT ... VALUES[/FONT] and [FONT="Courier New"]INSERT ... SELECT[/FONT] statements. You've left out the column names. Each of those makes the statement syntactically invalid. If you had included column names, either the [FONT="Courier New"]VALUES[/FONT] clause would have one too many column values or the SELECT clause would have one too few. In the [FONT="Courier New"]VALUES[/FONT] clause, you've left two of the column values unquoted, which is a logic error (it can potentially cause runtime errors). The first column uses the wrong quotes.
Study the syntax of [URL="http://dev.mysql.com/doc/refman/5.1/en/insert.html"][FONT="Courier New"]INSERT[/FONT][/URL] more closely.