Duplicate all rows in the table and prevent duplicate keys

Question

I'm trying to do it

• Get all blog posts with the table name.
• Copy them to a temporary database
• Edit the language field of this temporary table entry
• Insert into blog table

And I try it like this:

CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; INSERT INTO blogs SELECT * FROM tmptable; dump database tmptable; 

But from the corsa I get a duplicate key error ...

How can I prevent this?

-Edit -

I TRIED:

 CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; ALTER TABLE tmptable DROP id; INSERT INTO blogs SELECT * FROM tmptable; dump database tmptable; 

But then Column count doesn't match value count at row 1

-Edit -

I believe this will work (And that was because I know how many records exist)

 CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; UPDATE tmptable SET id = id + 1000; INSERT INTO blogs SELECT * FROM tmptable; 

But how can I do it right? (just set the following auto-increment value for the primary key (id) (without PHP / the same))

-Edit -

maybe something like this ???

 CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; UPDATE tmptable SET id = id + (SELECT id FROM blogs ORDER BY id DESC LIMIT 1); INSERT INTO blogs SELECT * FROM tmptable; 

Answer 1

 CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; alter table tmptable drop column id; INSERT INTO blogs SELECT NULL,tmptable.* FROM tmptable; 

The column id is assumed to be the first col.

Answer 2

No temporary table required.

 INSERT INTO blogs (lan, col1, col2, col3, ...) SELECT 1, col1, col2, col3, ... FROM blogs WHERE lan = 2 

Replace col1, col2, col3, ... with a list of all columns except lan and id .

Answer 3

Please try to execute sql. Similar SQL FIDDLE

  CREATE TEMPORARY TABLE tmptable SELECT * FROM blogs WHERE lan = 2; UPDATE tmptable SET lan = 1; UPDATE tmptable SET id = (select @val: =@val +1 from(select @val:=(select max(id) from blogs)) t) INSERT INTO blogs SELECT * FROM tmptable; 

Hope this helps.

Answer 4

 UPDATE blogs SET lan = 1 WHERE lan = 2; 

Just run this query in your source table.

I do not want to change the language, I want to save another copy of all entries and assign these copies to another language

In this case, transfer the primary key from your temporary table. When you insert rows back, do not include the primary key column:

 INSERT INTO blogs (title, lan) SELECT * FROM tmptable; 

Answer 5

Using a prepared statement, you can query the information scheme for the column that you want to use, and then fake the query that you want to execute:

Here is an example for your case:

 -- first query all the blogs column minus id and lan SELECT GROUP_CONCAT(c.COLUMN_NAME) INTO @cols FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'blogs' AND c.COLUMN_NAME not in ('id', 'lan'); -- second build the query with the gathered columns -- like INSERT INTO blogs(lan, col1, ...) SELECT 2, col1, ... FROM blogs WHERE lan=1 SET @sql=CONCAT(CONCAT(CONCAT(CONCAT('INSERT INTO blogs (lan,', @cols), ') SELECT 2,'), @cols), ' FROM blogs WHERE lan=1'); -- prepare the statement PREPARE stmt FROM @sql; -- and last run the insert EXECUTE stmt;