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

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 2

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 3

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 4

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

Answer 5

if your column is not an auto_increment you can use a temp table

create temporary table temp_user 
as 
select * from user WHERE pkcolum='7'; 
update  temp_user set pkcolum='7b'  where pkcolum='7';
insert into user select * from temp_user
drop temporary table user

so in this way you can copy all data in row pkcolum='7' and then assign new value '7b'