Does Anyone Know SQL

[incman]

Hi

Does anyone know sql enough to lend me a hand.

I just want to know how I can simply locate the last record in a table and the move that record into a new table.

I've been trying for a couple of weeks now and no success.

Thanks in advance.

[MaXiMiUS]

Here's some PHP that may help:

<?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);
}
?>

Edit: What you're asking to do -could- be done with only SQL, but I don't think it could be done in a single query. You'd basically have to do the same thing the PHP code is doing here.

[zen-r]

This thread shouldn't be in the Off-Topic category.

[Kayos]

This thread shouldn't be in the Off-Topic category.

Reporting the post helps us moderators put threads in the correct place.

-Moved to Programming Help-

[merrillmck]

http://www.w3schools.com/

http://www.w3schools.com/sql/sql_intro.asp

[zen-r]

Reporting the post helps us moderators put threads in the correct place.

-Moved to Programming Help-

I've always wondered about that.

So it's OK to use the "Report Post" icon for that then?

It says "Note: This is ONLY to be used to report spam, advertising messages, and problematic (harassment, fighting, or rude) posts." & since this doesn't come under any of those categories, I didn't want to use the Report Post icon if I wasn't supposed to.

What about threads that we think should be closed (not because they're spam or abusive, but for other reasons -eg(1) the poster is starting duplicate threads of the same question/ topic, or eg(2) because an original offer has closed but noobs keeps thinking the offer is still running & keep adding new posts to the end of a thread)? Should the icon be used then also? Or should we just wait for a Mod to find those threads themselves?

[Kayos]

I've always wondered about that.

So it's OK to use the "Report Post" icon for that then?

It says "Note: This is ONLY to be used to report spam, advertising messages, and problematic (harassment, fighting, or rude) posts." & since this doesn't come under any of those categories, I didn't want to use the Report Post icon if I wasn't supposed to.

What about threads that we think should be closed (not because they're spam or abusive, but for other reasons -eg(1) the poster is starting duplicate threads of the same question/ topic, or eg(2) because an original offer has closed but noobs keeps thinking the offer is still running & keep adding new posts to the end of a thread)? Should the icon be used then also? Or should we just wait for a Mod to find those threads themselves?

It's fine to report a message saying that a thread is in the wrong place, especially if you see a thread in the same place for days on end. I try to move everything eventually but I can't be here all day long.


And with reporting closing threads:

1. Duplicate threads can be considered spam so yes you can report them.

2. Those type of threads are solely at the discretion of the moderators.

If you would like to discuss this more please PM me.

[misson]

Edit: What you're asking to do -could- be done with only SQL, but I don't think it could be done in a single query. You'd basically have to do the same thing the PHP code is doing here.

It can be done in two by using INSERT ... SELECT followed by DELETE, but (as you surmise) not one.

This looks like it needs transactions to prevent duplicates in the tables if the DELETE fails to execute.

START TRANSACTION;
INSERT INTO target SELECT * FROM source ORDER BY col DESC LIMIT 1;
DELETE FROM source ORDER BY col DESC LIMIT 1;
COMMIT;

As long as there's an index on col, the two ORDER BY clauses won't drag down performance.

[Twinkie]

It can be done in two by using INSERT ... SELECT followed by DELETE, but (as you surmise) not one.

This looks like it needs transactions to prevent duplicates in the tables.

START TRANSACTION;
INSERT INTO target SELECT * FROM source ORDER BY col DESC LIMIT 1;
DELETE FROM source ORDER BY col DESC LIMIT 1;
COMMIT;

As long as there's an index on col, the two ORDER BY clauses won't drag down performance.

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 condition
LIMIT 1;

DELETE
FROM source
WHERE condition
LIMIT 1;

This would probably be faster because it eliminates the need for a separate query.

About the transactions I am not sure. I thought that it required logic in PHP in order for you to decide whether to issue a ROLLBACK command when you start a transaction? Or maybe the BEGIN - ROLLBACK - COMMIT syntax is specific to MySQL.

Make sure you have the appropraite table type (InnoDB) for transactions which must be specified in the table's creation. The default MyISAM will not work with transactions.

TABLE (
....
) type=innodb;

[misson]

It is good practice misson to separate subqueries with (),

The SELECT in INSERT ... SELECT isn't exactly a subquery, so I didn't bother.

but I think a SELECT INTO statement would be better suited to this.

MySQL doesn't support SELECT ... INTO.

This would probably be faster because it eliminates the need for a separate query.

Both SELECT ... INTO and INSERT ... SELECT require a search and an insert, and will thus perform similarly.

About the transactions I am not sure. I thought that it required logic in PHP in order for you to decide whether to issue a ROLLBACK command when you start a transaction? Or maybe the BEGIN - ROLLBACK - COMMIT syntax is specific to MySQL.[/CODE]

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.


@OP: another reason transactions are necessary here is that when you use a single query to copy a row from one table to another, you don't get a unique field that you can use in a DELETE query. Instead, you have to use the same selection clause in both statements. Another query could execute between the INSERT and DELETE, changing the row matched by the selection clause and thus causing the wrong row to get deleted.