多个ON DUPLICATE KEY UPDATE值

Question

This is my code:

INSERT INTO titles_production_companies (production_companies_name, production_companies_tmdb_id, title_id)
values
    ('United Artists', '60','1'),
    ('Achte Babelsberg Film', '6100','1'),
    ('Metro-Goldwyn-Mayer (MGM)', '8411','1'),
    ('Bad Hat Harry Productions', '9168','1')
ON DUPLICATE KEY UPDATE
    title_id=LAST_INSERT_ID(title_id),
    production_companies_name='United Artists',
    production_companies_tmdb_id='60',
    title_id='1',
    production_companies_name='Achte Babelsberg Film',
    production_companies_tmdb_id='6100',
    title_id='1',
    production_companies_name='Metro-Goldwyn-Mayer (MGM)',
    production_companies_tmdb_id='8411',
    title_id='1',
    production_companies_name='Bad Hat Harry Productions',
    production_companies_tmdb_id='9168', title_id='1'; 

and I've got this message:

Integrity constraint violation: 1062 Duplicate entry '1-Bad Hat Harry Productions-9168' for key 'uc_production_companies''

Answer 1

The answer in your situation is likely more related to your (My?)SQL server than to PHP.

Let's think of the steps your code is instructed to execute:

1. Run a INSERT statement
2. Check if there is any duplicate key in the statement
3. If duplicate key is detected, fill some of the fields with the given (fixed) values.

Now, if you have a UNIQUE index on any of the fields you fill in by your ON DUPLICATE clause, this will most likely be duplicated on any triggered duplicate INSERT statement.

Looking at your example, it seems you have an index of UNIQUE on your production_companies_name column which is violated on all ON DUPLICATE triggers, so only the first would work and all the rest will throw out this error.

---

Possible solutions:

1. Remove the UNIQUE type index from the production_companies_name column, if any.
2. Do not attempt to fill in any UNIQUE index with static values.
3. Do not attempt to fill in any UNIQUE index at all.

Let me know if that helps :-)