How to INSERT INTO...SELECT with ON DUPLICATE KEY

Question

I have two tables with identical structure. Table A contains all the current ads, Table B contains the archived ads. Column 1 (ad_id) is Primary Key, AI, INT. Table engine is MyISAM.

I need to copy all the table A ads preceding a certain date to the archive, table B. My goal is that all fields except ad_id are duplicated, ad_id should get auto-incremented. Here is what I have attempted:

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

Table B has many thousands of ads, Table A gets flushed often enough that the unique id field has low numbers that often duplicate the id's in Table B. So MySQL chucks a wobbly and tells me I have a Duplicate entry '8577' for key 'PRIMARY'.

So I made several attempts to get past that:

First I tried selecting the individual columns to insert, setting ad_id to 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

That results in the error #1241 - Operand should contain 1 column(s), which goes away if I use the wildcard * selector, but then I get the duplicate error.

Next I tried SELECT LAST_INSERT_ID(), which always returns 0.

Then I tried a few using ON DUPLICATE KEY UPDATE, but I can't seem to get that to work.

I even tried to grab the highest id with:

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

This works for exactly one row, then results in a duplicate entry again (since @max is a static variable).

What am I doing wrong here? Am I making this way too difficult?

the way you flush table, why not just keep current id's max value ? (`delete from A` should be enough) — zb', Feb 05 '13 at 05:22

score 1 · Accepted Answer · answered Feb 05 '13 at 05:31

1

in your case why not use ?

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

answered Feb 05 '13 at 05:31

zb'

8,071
4
41
68

This query results in `#1241 - Operand should contain 1 column(s)`. I actually have 32 identical columns, I shortened the example for brevity. Turns out there are 5 other KEY columns for indexing purposes, could that be the issue? – Gary D Feb 05 '13 at 17:24
I removed the parens from the SELECT clause and it worked. Thanks to this post: [link](http://stackoverflow.com/questions/456644/mysql-syntax-error-message-operand-should-contain-1-columns) – Gary D Feb 05 '13 at 17:38

Shrey · Answer 2 · 2013-02-06T06:06:25.347

you may drop the primary key constraint on your ad_id of your table B using th following command.

ALTER TABLE B DROP PRIMARY KEY

Then try your usual query i.e.

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

UPDATE 1

if you dont want to have multiple ad_id then you may directly try this query

   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);

Here is the SQL Fiddle

Hmmm, this would result in duplicate id columns, correct? Not sure what the effect would be on the application. Worth trying on a copy I suppose... — Gary D, Feb 05 '13 at 17:27

How to INSERT INTO...SELECT with ON DUPLICATE KEY

2 Answers2

Linked