This isn't a homework assignment, I'm coding a browser-based game. Currently we have very few playtesters, so getting enough people to join a 3 way doubles match is difficult, and right now when a doubles match starts, it crashes on game commencement (post-player joining). It's bugs in the PHP script that I need to debug. In order to action this, I've placed a 6 player game on "pause", as it has only 5 players in it. I plan on copying this game, then joining it with my test account in order to force errors out of the code, and systematically bugfix. Each time I locate and fix a bug, I'll need to copy the game again. Understandably, this is a process which could take a long time, so I'll need a systematic / programmatic method of copying the game.
Here's the catch:
The game information is spread across about 6 InnoDB MySQL tables, with one table being the primary key, therefore cascades being in place for the other tables. Given that the "parent" table name is $SQL_GAME_PRIMARY, what would be the command to copy the "paused" game row (game_id=4) to a new row (game_id=5)? Is it possible to do this without having to run a command for every child table? Is it possible to do this with wildcards, thereby avoiding referring to individual columns (aside from the column 'game_id')?
SELECT INTO looks promising, but I'm not very savvy with MySQL (PHP / JS are my forte), and I don't want to corrupt the MySQL tables with a dodgy command I put together ignorantly myself.
SQL Duplicate a row in a table
Page 1 of 1
Copying the contents of one row to another row in the same SQL table
2 Replies - 3056 Views - Last Post: 14 April 2010 - 07:04 AM
Replies To: SQL Duplicate a row in a table
Re: SQL Duplicate a row in a table
Posted 14 April 2010 - 06:31 AM
Given that the "parent" table name is $SQL_GAME_PRIMARY, what would be the command to copy the "paused" game row (game_id=4) to a new row (game_id=5)? Is it possible to do this without having to run a command for every child table? Is it possible to do this with wildcards, thereby avoiding referring to individual columns (aside from the column 'game_id')?
Short answers: there is none, no, and no.
There is no quick and easy way to copy a table row along with all the rows that are linked to it via foreign keys. Obviously you can do it, but not in one line. Unless you want to get really fancy (which you probably don't), or unless your DBMS has some proprietary extension for this (I'm not aware of any that do), you're not going to get away with less than one statement per table. You can certainly do the insert by using a subquery (such as INSERT INTO table (field1, ...) SELECT field1, ... FROM table WHERE...), but wildcards probably aren't going to do you much good. You can only use * to represent every column of a table, so it won't work if want to exclude some.
So you're just going to have to do it the "long" way. If I were you, I'd just figure out the INSERT queries you need encapsulate them a stored procedure or even a PHP script. Using 6 queries isn't so bad if you can just run a script for it later.
Page 1 of 1