Moving a row between tables - possible?

[RobinDoh]

Is there a command that will move an entire row between two tables with the same structure?

Something like this would be useful:

MOVE FROM table1 TO table2 WHERE this=that

Thanks.

[tomhath]

Nope. WIth a dbms that supports TRANSACTIONS you can do it atomically with something like:

BEGIN TRANSACTION
insert into table2 select * from table1 where this = that
delete from table1 where this=that
COMMIT

[Frag]

Depends.. What database are you using? You can do it with subselects.

But if your just building a new table I would just dump the data then reload it into the new table.

Frag

[RobinDoh]

Using MySQL, I've done it like this now:

	$query = "SELECT * FROM users WHERE $type = '$input'";
	$result = mysql_query($query);
		while ( $row = mysql_fetch_array($result) ) {
				$how = "by admin ".$playername." for reason: ".$reason;
				mysql_query("INSERT INTO users_deleted VALUES('$row[id]', '$row[email]', '$row[password]', '$row[name]', '$row[gender]', '$row[subspecies]', '$row[age]', '$row[cash]', '$row[bank]', '$row[attack]', '$row[defence]', '$row[strength]', '$row[speed]', '$row[accuracy]', '$row[maxhp]', '$row[currenthp]', '$row[level]', '$row[currentexp]', '$row[maxexp]', '$row[energy]', '$row[weapon]', '$row[armor]', '$row[posts]', '$row[moderator]', '$row[admin]', '$row[manager]', '$row[battleswon]', '$row[battleslost]', '$row[battlesdrawn]', '$row[battlestotal]', '$row[lastlogin]', '$row[lastkilled]', '$row[lastkilledby]', '$row[gymmember]', '$row[giftspeed]', '$row[giftstrength]', '$row[lastip]', '$row[lastcookie]', '$row[lastbrowser]', '$row[dodgy]', '$row[referrer]', '$row[referrals]', '$row[connection]', '$row[mbsig]', '$how')") or die(mysql_error());
				$deleteq = "DELETE FROM users WHERE $type = '$input'";
				mysql_query($deleteq);
		}

:rolleyes:

[[deleted]]

A different question: why do you want to do this at all? Having multiple tables of the same layout usually indicates a bad database model.

Why don't you just add a 'deleted' column to the original table and mark the users as 'deleted' that way?