Duplicate all rows in a table and prevent duplicate keys

Question

I am tring to do this

• Get all rows in a blogs named table.
• Copy them in a temporary database
• Edit the language field of this temporary table records
• Insert into the blogs table

And I'm trying 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 of corse I get duplicated key error...

How Can I prevent it?

-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 the Column count doesn't match value count at row 1

-EDIT-

I believe this will work (And it Did, cause 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 properly? (just set the next avaliable autoincrement value for primary key(id) (without PHP/alike))

-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

No temporary table needed.

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 2

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;

Assumed, the column "id" is the first col.

Answer 3

UPDATE blogs SET lan = 1 WHERE lan = 2;

Simply run that query on your original table.

I don't want to change the language, I want to save another copy of all the records and asign this copies a different language

In that case, drop the primary key from your temporary table. When you insert back the rows, don't include the primary key column:

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

Answer 4

Please try following sql. A 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.