Mysql, Copy table row within the table itself

Suppose you have table x created as such:

create table x (
      pkey integer primary key auto_increment,
      foo varchar(3)
);

Then, let's populate it with a row:

insert into x (foo) values ('bar');
commit;

Now, we can copy the data using stuff like:

insert into x (foo) select foo from x where pkey = 1;
commit;

Hope this helps?

If the table you are dealing with does NOT have a primary key or a UNIQUE index, then this should work

insert into tableName
select * from tableName

If the table DOES have a unique or primary key (say)

primaryKey
field1,
field2

then you need to omit that field

insert into tableName ( field1, field2 )
select field1, field2 from tableName

and so on....

BTW, where it says


insert into tableName
select * from tableName

That is ONE statement, not two. I should have written it as

insert into tableName
     select * from tableName


or


insert into tableName select * from tableName

I wouldn't say that "select *" exactly matches the "copy _this_ row"-requirement. I see the problem however; if the table has now primary or unique keys, you really can't tell what "this row" is with any level of certainty (then again, why would anyone ever create such a table escapes me:)

OK I get the point about taking out the right row if there are no unique field. However there are an unique key.

DiscoNova,
AS I understood from other q/a on this problem, the * isn't available in this type of INSERT, because it's also takes the key itself - which means that this ** query needs to take all fields names as source and all fields names as destination, with '' marks for dates and text.. humz right?.

Thank's disconova, i've founded that out pretty soon after the comment :p. The * thing does not work though.

The * thing does work within the constraints I listed, however it may not work on MySQL 4 or MySQL 3 which were much more restrictive about subselects. I know it works on MySQL 5 because I use it more or less as posted here....

Regards

Hi again! Hopes I can sneak in a follow-up question here. I also want to copy the data, not only the definition.. is that easy as this? :)

@dingir: Umm.. I don't really understand what you mean by this? The above statement was meant for copying the data.

Disconova, whops. Tired last evening, i think. I was looking at another question at same time, that copying the definition of a table to another table. I creating a new query, keep eyes open.