Duplicate entry 'xxxx' for key 'PRIMARY'

Databases
1

I’m a novice at MySQL so please excuse my shortcomings. I am trying to work in PHPMyAdmin.

I am working with a Joomla website. In my menu table I want to import a number of rows from another table in another database to add more menu items.

I have a QUICKTOOLS menu which you can see on the right side of the page at https://colingrayca.com/services

I want to add it to another website which you can see here https://abwddemo.com/services. You can see that the space is there for the QUICKTOOLS menu but I cannot import the data into the database.

I was not able to import the sql file into the table so I just imported the table into the database and tried to copy all of the rows from the imported table to the menu table, but I get the error Duplicate entry ‘xxxx’ for key ‘PRIMARY’

After reading what I can find, and comprehend, I am no further ahead.

I start in the DB I want to take the rows from with the following statement

SELECT * FROM abcde_menu WHERE menutype=“quicktools”

From there I just import it into my Database: abwddemo_data, so I end up with a table that is foreign to the database.

I then try to copy the rows with this statement

INSERT INTO jlbkx_menu SELECT * FROM abcde_menu, and that is where I get the error.

I was successful once a year ago but there were changes made to the source database so I have to move it all again. Problem is, I cannot figure out what I did right the last time.

Any help would be appreciated. Thank you

2

I’ve not tried this kind of data transfer, but looking at your query, the first idea that springs to mind is:-
Instead of SELECT *, you could select all columns except the ID.
The AI should give new IDs to the copied rows, and you won’t duplicate existing ones.

3

Thanks for the reply

Is there a command that selects all columns except the ID? If I have to identify all columns, it will be a very long statement and prone to errors no doubt.

4

Look at the table definition for jlbkx_menu. The Primary Key for this table has been set, and at least 2 records in abcde_menu contain the same value in that field.

5

I dropped the id column in abcde_menu which was the Primary column, then tried to copy the remaining rows into jlbkx_menu but got another error
#1136 - Column count doesn’t match value count at row 1

which I suspect means that the column count doesn’t match

6

because the abcd table has an ID column.

You need an ID column but either:

  1. Don’t make it a primary key
  2. Change the values in abcde table such that all values in the ID column are unique.
7

There are 120 rows. Do you mean change the Id value for each row?

my lack of knowledge is showing.

8

the error message tells you what you’re looking for. There are at least 2 rows in the abcde table with ID “xxxx”

9

I see. Actually, looking at the two tables, there are probably a few dozen that are the same.

Would I be able to export it to a CSV file and change all the Id’s there, then import it back into the database?

10

If I am successful at this, there are 70 other websites where this menu needs to be imported into. Hence I need to find a way to do this easily vs. building the menu in each website

11

You should be listing out the columns in both the INSERT and the SELECT part of the query, so that if the structure of either table gets changed, the query will still work.

Export a .sql dump of each table. The INSERT … query within the .sql file lists all the columns. You can just copy/paste this into your code and remove the column(s) you don’t want.

12

OK, thank you. I will work on this.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.