copy row

mhalloran

ok, I have a master table and a temp table with identical fields. The user is going to be entering data into his/her row of the temp table, piece by piece. At the very end, I want the user to be able to copy the whole row into the main table. There are over a hundred fields so I don't want to have to grab all the information and then insert it, field by field. I'm looking for something like:

INSERT INTO master_table as SELECT * FROM temp_table where id=3

Any ideas?

mhalloran

well I almost had it - this works....

instert into temp master_table select * from temp_table where id=3;

the problem is that the primary keys are screwing it up. It will ender 3 into the id of the master table and replace the contents of the previous record that had a an id of 3.

Let's say that these are my fields:
id int not null auto_increment primary key
first_name varchar(10)
last_name varchar(10)

both the temp table and master table have identical fields but when I copy the row over, I want the master table to make its own primary key. I want to use the exact statement as above except I want to select everything but the key and add to all fields but the key.

Lars_Berg

Omit the id, thus will Mysql assign a new unique value to the column

insert into masterTable(firstname,lastName) 
select firstName,lastName from temp_table where id=3;

Just start typing.

mhalloran

All sounds good except that there are 170 fields that keep changing. What I do now is select all the fields, throw them into an array, remove the id and then use a select-insert statement like the one above.

Thanks for the reply.

sfullman

I'm not going to give you any specifics, but this is very important to consider through your site structure:

If table A has primary keys 1-100, and table B has primary keys 1-25, you're adding 25 records. You will have to omit the ID, and yes, the new records will be indexed with ID=101 to 125.

BUT, if you have ANY field in ANY table in the database or script that refers to that primary key, you'll also need to update those as well.

This takes a lot of planning to make sure the transaction happens the right way. An encouragement to stay away from multiple tables that are similar.

Sam Fullman
Compass Point Media