Copy row with new auto-increment value

Databases
1

Table with 1 row of data :


CREATE TABLE testtable (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO testtable (name) VALUES  ('data1');

In the first row ‘id’ will be ‘1’

Now I want to copy that row so I get a second row, with all the data the same as the first row, but it should have id ‘2’

This is what I was thinking of :

INSERT INTO testtable SELECT * FROM testtable WHERE ID=1 ;

But this gives an error, because it tries to insert a value that should be unique.

So is there a way to get around this ? To copy a row and let it have an auto-incremented value at the same time ? Thanks

2

You gave the answer yourself :slight_smile:

INSERT INTO testtable (name) VALUES (‘data1’);

You can use the same insert if the data need to be the same

3

let me guess… the row that you want copied is some kind of template? i’m guessing there are many more columns besides name

4

Donboe : Ok, but that was just some code to show how the table was built and what could be in it.

In reality there will be about 50 columns, and they will be filled long before I have to do the part with the copy. I also don’t know exactly what columns will be used in the final version of the first part. So that’s why I would not like to do a select query on all columns of one row, and insert them back with a new id. I want to play safe, and copy everything that there is at that moment, and get a new row with a new auto-incremented id.

I’m currently testing out using a normal MEDIUMINT and decide the exact value in the php script self, so without auto-increment. But that doesnt seem perfect either.

5

Ok cant you use

SELECT row_1 FROM TABLE FOR UPDATE
UPDATE TABLE SET row_2 = row_1

6

what happened when you tested that statement? :wink:

7

I haven’t, but i guess its not working :blush:

8

I happened to come across the perfect solution for my problem now :

http://www.php.net/manual/en/function.mysql-fetch-array.php#92513

(just posting in case anyone ever has same problem and searches and ends up here)

9

foggy at the moment but would this not do it?


insert into table_name
(col2, col3, col4 )
select col2
       , col3
       , col4
   from table_name
where id = 3
10

I guess that would work. You would need to manually decide the next id by first selecting (max) id and then do +1, so auto-increment should be off. And it also requires you to know all the needed column names from the start, wich didnt really suited my specific situation. I wanted to select just everything except the id, no matter how much or little it was.

11

You would need to manually decide the next id by first selecting (max) id and then do +1, so auto-increment should be off

ugh?? If you insert to a table, which has the pk id set to auto increment, it will surely, erm, auto increment?

maybe I mis-understood something.

12

somebody for sure did, and it wasn’t you :slight_smile: