ERROR 1062 (23000): Duplicate entry '37205' for key 'citystate.PRIMARY'

Question

In this image is the same error code of my title, I created a new table successfully and what I had to do was insert data from another table to this new table. For the first time it worked, but when I had to insert data from a second table into the new table, I got this error code. What can I do to avoid that?

This is was what I used to create the table

CREATE TABLE cityState (
    city VARCHAR(90) NOT NULL,
    state CHAR(2) NOT NULL,
    zipCode CHAR(5) NOT NULL UNIQUE,
    primary key (zipCode)
);

This next set of commands worked

INSERT INTO cityState (city, state, zipCode)
SELECT city, state, zipCode
FROM crew;

This next set of commands gets the error code, I'm trying to fix the whole duplicate thing basically

INSERT INTO cityState (city, state, zipCode)
SELECT city, state, zipCode
FROM passenger;

Does this answer your question? [INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE](https://stackoverflow.com/questions/2472229/insert-into-select-from-on-duplicate-key-update) — Ahmed Yousif, Feb 07 '23 at 20:18

ysth · Answer 1 · 2023-02-07T20:23:42.247

2

If you want to just ignore duplicate zipcodes, change INSERT to INSERT IGNORE.

But your whole table design is confusing to me; some cities have multiple zipcodes and some zipcodes have multiple cities. If you get rid of the unique constraint on zipcode, you may want to just do:

INSERT INTO cityState (city, state, zipCode)
    SELECT city, state, zipCode FROM crew
    UNION DISTINCT
    SELECT city, state, zipCode FROM passenger

edited Feb 07 '23 at 20:23

answered Feb 07 '23 at 20:18

ysth

96,171
6
121
214

+1 for pointing out the flawed table design. This needs more thought, but I don't know what the asker's level of knowledge is. – Michael Buckman Feb 07 '23 at 20:34

Michael Buckman · Answer 2 · 2023-02-07T20:30:09.780

It is somewhat difficult to know for sure exactly what is going on without some examples of the data you are trying to copy from one table to another, but looking at your SQL, you've declared the zipCode field of your ctyState table to be UNIQUE and serve as the PRIMARY KEY. My guess is that some record from your passenger table has a zipCode that already exists in the cityState table and therefore the UNIQUE index is preventing it from being added because it already exists and therefore would cease to be unique.

Keep in mind that there are cases where a single zip code could serve more than one city. These cases are rare, but they do exist. For example, the zip code 94608 in California is used for both Emeryville and parts of Oakland.

ERROR 1062 (23000): Duplicate entry '37205' for key 'citystate.PRIMARY'

2 Answers2