Copying rows in MySQL

Question

I want to copy all of the columns of a row, but not have to specify every column. I am aware of the syntax at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html but I see no way to ignore a column.

For my example, I am trying to copy all the columns of a row to a new row, except for the primary key.

Is there a way to do that without having to write the query with every field in it?

Answer 1

If your id or primary key column is an auto_increment you can use a temp table:

CREATE TEMPORARY TABLE temp_table 
AS 
SELECT * FROM source_table WHERE id='7'; 
UPDATE temp_table SET id='100' WHERE id='7';
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;

so in this way you can copy all data in row id='7' and then assign new value '100' (or whatever value falls above the range of your current auto_increment value in source_table).

Edit: Mind the ; after the statments :)

Answer 2

You'll need to list out the columns that you want to select if you aren't selecting them all. Copy/Paste is your friend.

Answer 3

This is a PHP script that I wrote to do this, it will assume that your first col is your auto increment.

$sql = "SELECT * FROM table_name LIMIT 1"; 
$res = mysql_query($sql) or die(mysql_error());
for ($i = 1; $i < mysql_num_fields($res); $i++) {
     $col_names .= mysql_field_name($res, $i).", ";
 }
 $col_names = substr($col_names, 0, -2);

$sql = "INSERT INTO table_name (".$col_names.") SELECT ".$col_names." FROM table_name WHERE condition ";
$res = mysql_query($sql) or die(mysql_error());

Answer 4

If you don't specify the columns you have to keep the entries in order. For example:

INSERT INTO `users` (`ID`, `Email`, `UserName`) VALUES
(1, '[email protected]', 'StackOverflow')

Would work but

INSERT INTO `users` VALUES
('[email protected]', 'StackOverflow')

would place the Email at the ID column so it's no good.

Try writing the columns once like:

INSERT INTO `users` (`Email`, `UserName`) VALUES
('[email protected]', 'StackOverflow'),
('[email protected]', 'StackOverflow2'),
('[email protected]', 'StackOverflow3'),
etc...

I think there's a limit to how many rows you can insert with that method though.

Answer 5

No, this isn't possible.

But it's easy to get the column list and just delete which one you don't want copied this process can also be done through code etc.

Answer 6

Copy the table to a new one, then delete the column you don't want. Simple.

Answer 7

I'm assuming that since you want to omit the primary key that it is an auto_increment column and you want MySQL to autogenerate the next value in the sequence.

Given that, assuming that you do not need to do bulk inserts via the insert into ... select from method, the following will work for single/multi record inserts:

insert into mytable (null, 'a', 'b', 'c');

Where the first column is your auto_incremented primary key and the others are your other columns on the table. When MySQL sees a null (or 0) for an auto_incremented column it will automatically replace the null with the next valid value (see this link for more information). This functionality can be disabled by disabling the NO_AUTO_VALUE_ON_ZERO sql mode described in that link.

Let me know if you have any questions.

-Dipin