MySQL Row Duplication

[fnbcprog]

Is there anyway to duplicate an existing row without have to fetch the original row and then do a totally separate insert statement?

This is what I am trying to do...

I am working on a library and when the user wants to update an existing document the application archives the existing document and adds a document to the library with the same exact properties as the old one.....

Any help.....

[vidago]

you may try this..

http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

hope it help.

[fnbcprog]

Yeah the olny problem is that I am not working on the version on MySQL that the function SELECT...INSERT comes with.....

[tsinka]

Hi,

which MySQL version do you have ?

Thomas

[fnbcprog]

3.23.55

[tsinka]

This MySQL version supports INSERT INTO ... SELECT, but it doesn't work in this case since something like

INSERT INTO thetable (fieldlist) SELECT fieldlist FROM thetable

is invalid. The table names must differ, you can't insert data into a table selected from the same table.

Solutions:

1. use a temporary table ... overkill
2. fetch the record and afterwards insert the record
   3.
   Create an archive table like tblDocumentArchive with the same structure, then do something like INSERT INTO tblDocumentArchive SELECT * FROM tblDocument WHERE lngDocumentID='<theid>'
   Update the record in the document table afterwards.

Do you store documents and archived versions in the same table ?

Thomas