• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4347
  • Last Modified:

Mysql, Copy table row within the table itself

Hi! I want a simple "copy this row"-function. How do I do this? Mysql and ASP scripting.

An INSERT query would be fine because I can use that both in my dev environment and at the customers administration area when finished,
0
dingir
Asked:
dingir
  • 4
  • 4
  • 3
1 Solution
 
DiscoNovaCommented:
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?
0
 
Beverley PortlockCommented:
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....
0
 
Beverley PortlockCommented:
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

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
DiscoNovaCommented:
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:)
0
 
dingirAuthor Commented:
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?.
0
 
DiscoNovaCommented:
I haven't tested it recently, but if the primary/unique key in the table is an auto_increment-field, you should be able to use "select * from xxx where key=something" (at least older versions of MySQL worked that way; no matter what you _inserted_ into the key, it got contents from auto increment anyway ... if you wanted some other value, you needed to update the row (though updating key field is not usually wise on my opinion)).

Also, if you prefer (or need to use) the select everything but the key, you can just use the field names; they already are of the correct type.
0
 
dingirAuthor Commented:
Thank's disconova, i've founded that out pretty soon after the comment :p. The * thing does not work though.
0
 
Beverley PortlockCommented:
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

0
 
dingirAuthor Commented:
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? :)
0
 
DiscoNovaCommented:
@dingir: Umm.. I don't really understand what you mean by this? The above statement was meant for copying the data.
0
 
dingirAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now