CODEWITHSUNDEEP
mysqlinsertauto-increment
dokhebi
dokhebi

Reputation: 221

Insert into table forcing new primary key

In MySQL I want to duplicate a couple of records but because there are are large number of columns I don't want to list every single column name from the table in the insert. I tried doing an INERT INTO with an ON DUPLICATE KEY clause but it didn't work. I'm copying the records into a temporary table first. The setup looks like this:

CREATE TABLE copy_of_test LIKE test;
INSERT INTO copy_of_test SELECT * FROM test WHERE some_column IN (@X, @Y, @Z);
UPDATE copy_of_test SET some_other_column = @NewValue;
INSERT INTO test SELECT * FROM copy_of_test
ON DUPLICATE KEY UPDATE test.idTest = last_insert_id(test.idTest);

The last INSERT would show:

Query OK, 0 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

But no records are inserted into test. I tried various other versions of this query but only got error messages about syntax or ambiguity of column names. What am I missing?

What I really want is something like this:

INSERT INTO test SELECT * FROM copy_of_test
ON DUPLICATE KEY AUTO_INCREMENT test PRIMARY KEY;

Upvotes: 0

Views: 229

Answers (1)

Hauke P.
Hauke P.

Reputation: 2823

Here's a (very) quick and dirty way that should work:

CREATE TABLE copy_of_test LIKE test;
INSERT INTO copy_of_test SELECT * FROM test WHERE some_column IN (@X, @Y, @Z);
UPDATE copy_of_test SET some_other_column = @NewValue;
UPDATE copy_of_test SET idTest = idTest + (SELECT MAX(idTest) FROM test); -- here's where the magic happens
INSERT INTO test SELECT * FROM copy_of_test

Edit - Here's another similar quick'n'dirty way that won't use a subquery: (This won't work if there are other uniqueness restrictions.)

CREATE TABLE copy_of_test LIKE test;
ALTER TABLE copy_of_test DROP PRIMARY KEY, MODIFY idTest INT;
ALTER TABLE copy_of_test ALTER idTest DROP DEFAULT;
INSERT INTO copy_of_test SELECT * FROM test WHERE some_column IN (@X, @Y, @Z);
UPDATE copy_of_test SET some_other_column = @NewValue, idTest = null;
INSERT INTO test SELECT * FROM copy_of_test

Still, in my opinion you should not use such kinds of tricks just to get around having to write down all column names. Just creating a temporary table for a query that could also be made with a single statement is definitely no good solution. You might want to find a way to create the statement programmatically. SHOW COLUMNS IN test can be your friend.

Upvotes: 1

Related Questions