asked on

Duplicate Records In MySQL

I had this question after viewing MySQL - I need to duplicate a record in same table, but alter some fields?.

I need the mysql query I can use with phpMyAdmin to duplicate records that have the same id_object in my existing table.

Here is my table
https://gyazo.com/506844c2ce0f7105cf3718a6e09d34b6

I want everything duplicated except for the id_object - these are links tied to a navigation menu on our site. Here is what the menu looks like.
https://gyazo.com/fdb4cb9d31dd5955fc4e0e87025d209a

the 199 is the page number and the navigation_links are the links you are seeing in the navigation menu

I have other pages that I want to duplicate the same links on so that is why I need to be able to duplicate these rows.

sharingsunshine

ASKER

I found this on the net and made a few modifications to it but I am getting an error that says:

Error

SQL query:

CREATE TEMPORARY TABLE ps.tmp;

MySQL said: Documentation
#1113 - A table must have at least 1 column

CREATE TEMPORARY TABLE ps.tmp; 
SELECT * FROM ps_navigation_link_index.bak WHERE id = 199;

UPDATE ps.tmp SET id=2809 WHERE id = 199;

INSERT INTO ps_navigation_link_index.bak SELECT * FROM ps.tmp WHERE id = 2809;

Open in new window

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharingsunshine

ASKER

I am still getting that error see screenshot
https://gyazo.com/e46368d117bf7252163fef52cf975352

chaau

Now the error tells you that the table "ps_navigation_link_index.bak" does not exist in your schema. Verify the correct name of this table

sharingsunshine

ASKER

I have. That's why I included the name in the previous screenshot. Best I can tell it is correct. Perhaps you can see something I am not seeing.

sharingsunshine

ASKER

You can't have a period in a table name so that's why it wasn't working. Also, making your change was needed too.

sharingsunshine

ASKER

Thanks for the help.