Как вставить... выбрать с помощью ключа дублирования

Question

У меня есть две таблицы с одинаковой структурой. Таблица A содержит все текущие объявления, таблица B содержит архивированные объявления. Столбец 1 (ad_id) - это первичный ключ, AI, INT. Настольный движок MyISAM.

Мне нужно скопировать всю таблицу объявлений A, предшествующих определенной дате, в архив, таблица B. Моя цель состоит в том, чтобы все поля, кроме ad_id, дублировались, ad_id должен автоматически увеличиваться. Вот что я пытался:

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) <= 2012

Таблица B содержит много тысяч объявлений, таблица A достаточно часто сбрасывается, так что поле уникального идентификатора имеет низкие числа, которые часто дублируют идентификаторы в таблице B. Таким образом, MySQL отбрасывает шаткий сигнал и говорит, что у меня есть Duplicate entry '8577' for key 'PRIMARY',

Итак, я сделал несколько попыток, чтобы обойти это:

Сначала я попытался выбрать отдельные столбцы для вставки, настройки ad_id в NULL:

INSERT INTO B(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (NULL, `ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

Это приводит к ошибке #1241 - Operand should contain 1 column(s), который исчезает, если я использую селектор подстановочного знака *, но затем я получаю ошибку дубликата.

Далее я попробовал SELECT LAST_INSERT_ID(), который всегда возвращает 0.

Затем я попробовал несколько, используя ON DUPLICATE KEY UPDATE, но я не могу заставить это работать.

Я даже пытался получить самый высокий идентификатор с:

SELECT @max := max(ad_id) FROM B;

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
  SELECT *
  FROM A
  WHERE YEAR( ad_expire ) <= 2012

ON DUPLICATE KEY UPDATE ad_id = @max + 1

Это работает ровно для одной строки, а затем приводит к дублированию записи (поскольку @max является статической переменной).

Что я здесь не так делаю? Я делаю этот путь слишком сложным?

Answer 1

В вашем случае почему бы не использовать?

INSERT INTO B(`ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (`ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

Answer 2

Вы можете удалить ограничение первичного ключа на вашем ad_id вашей table Bиспользуя следующую команду.

ALTER TABLE B DROP PRIMARY KEY

Затем попробуйте ваш обычный запрос, т.е.

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) <= 2012

ОБНОВЛЕНИЕ 1

если вы не хотите иметь несколько ad_id тогда вы можете напрямую попробовать этот запрос

   INSERT INTO `B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) <= 2012
   ON DUPLICATE KEY UPDATE
    ad_advertiser = VALUES(ad_advertiser), 
    ad_ln = VALUES(ad_ln), 
    ad_expire = VALUES(ad_expire);

Вот SQL Fiddle