How to move/copy the records to new person with different ID

rosy

Hi,
I would like to move/copy the records of a person to another.
For example, if a person is retired, i would like to move the DB records to new employee. Since new employee will have their own unique ID. How i can perform using Mysql.
Thanks.

liquorvicar

Have a look at INSERT...SELECT or UPDATE. One of those should be able to do what you're after. I can't be more specific as I'm not totally sure what you mean. If you can describe what you're trying to achieve in a bit more detail we might be able to give you a better answer.

rosy

I have to insert the same records of person1 to person2.
The ID of person1 is 107. I would like to insert the records of person1 to person2 with different ID say 110.
I have seen the syntax,
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
but how do i need to use based on my criteria.
INSERT INTO tbl_temp2(id=110)
SELECT * FROM tbl_temp1 WHERE tbl_temp1.id=107;
Will this create a new id 110 and insert all the values of 107?
Please help.
Thanks

unspace

Please try it，but I can't assure my answer
INSERT INTO new_table
( newpid, fields......)
SELECT pid,fields......
FROM old_talbe
WHERE condition

You can use 110 replace newpid like this
INSERT INTO new_table ( 110, fields......) SELECT pid,fields......FROM old_talbe WHERE pid = 107

liquorvicar

Unspace is not quite right but almost there. It depends on whether you have a row with ID 110 in your table or not.
If not, then

INSERT INTO table (field1,field2,field3) SELECT field1,field2,field3 FROM table WHERE id=107

This will insert a new row with a new ID into the table. Make sure you have the same number of columns in the INSERT and SELECT parts of the query. You can then use mysql_insert_id() to retrieve the ID of the inserted row.

If you have a row with that ID already, you can use ON DUPLICATE KEY. Can't give you the syntax as I've never used it myself.

Have a go and if you get stuck, post the query and the error you're getting back here.

rosy

Hi,
I tried the above coding, by create a test table called emptable2, with fields empid, clientname, lead.
CREATE TABLE emptable2(empid int not null, clientname varchar(25), lead varchar(10));
and inserted some values,
INSERT INTO emptable2 SET empid='1', clientname='client1', lead='lead1';
INSERT INTO emptable2 SET empid='1', clientname='client2', lead='lead2';
INSERT INTO emptable2 SET empid='1', clientname='client3', lead='lead3';
INSERT INTO emptable2 SET empid='2', clientname='client4', lead='lead4';
now i have four records with 3 records to empid='1' and one to empid='2'.
And then i tried the coding you suggested,
INSERT INTO emptable2 (empid,clientname,lead) SELECT * FORM emptable2 WHERE empid='1';
It displays the record, like
empid clientname lead
1 client1 lead1
1 client2 lead2
1 client3 lead3
2 client4 lead4
1 client1 lead1
1 client2 lead2
1 client3 lead3

But i want to insert the records of empid #1 to empid #2.
like
1 client1 lead1
1 client2 lead2
1 client3 lead3
2 client4 lead4
2 client1 lead1
2 client2 lead2
2 client3 lead3
How i can make it.
Please help, since i have lot of fields(75) so if it works i can change them easily.
please help
Thanks in advance.

liquorvicar

INSERT INTO emptable2 (empid,clientname,lead) SELECT 2,clientname,lead FROM emptable2 WHERE empid='1';

rosy

I tried with above code, its not working for me.
how i can copy records of one employee to another employee in the same table.
One employee with emp_id=200 is resigned and another new employee joined and his emp_id=300.
I have the table with a field set to auto increment and primary key.
CREATE TABLE emptable (sales_id int(11) NOT NULL auto_increment,
clientname varchar(200) NOT NULL default '',
emp_id int(11) default NULL,...........
PRIMARY KEY (sales_id),
KEY 'sales_id' (sales_id))
when i try to use INSERT ... SELECT command i encounter an error,
"duplicate entry "sales_id" for key1"
please help to fix this problem.

liquorvicar

Can you give some concrete examples of what you're trying to do? The example you gave didn't have a Primary Key so the code I supplied worked. Either look at my code from your last example and adapt it to your real example, or post some real examples so we can have a look.

rosy

I have an employee table having
fields like,
emp_id int not null auto_increment, primary key(emp_id),
emp_name varchar(35).

Another table contains data in which sales made by employees are stored in table called emp_sales.
I have some fields in emp_sales table like,
sales_id int not null auto_increment, primary key(sales_id),
emp_sid int null,
product_id int null,
itemno int null,
date_entered... and so on.

one of the employee has resigned and new employee joined.
I would like to have the records of the old employee in emp_sales to new employee.
When i tryed using INSERT...SELECT, since i have sales_id auto_increment, i could not able to
insert the records .Error says duplication.
How i can make it.
please help

liquorvicar

How about

UPDATE emp_sales SET emp_sid=$newValue WHERE emp_sid=$oldValue

Or if you need to keep the old records intact

INSERT INTO emp_sales (emp_sid,product_id,itemno,date_entered, etc) SELECT $newValue,product_id,itemno,date_entered, etc FROM emp_sales WHERE emp_sid=$oldValue

rosy

I prefer the above method, INSERT ... SELECT.
Instead of UPDATE. because i need to keep the old employee records .
Because emp_sid is set to auto_increment, i have to check the maximum emp_sid value and give the new value for each record?.
I have 75 fields, and 300 records , is this the only way i have to do. or any other method is available?

liquorvicar

From what you wrote emp_sid is not auto_increment, it's sales_id that is auto_increment.
What you need to do is to add your new employee and then use mysql_insert_id() to get the emp_id and use that in your INSERT...SELECT query.

rosy

Do i need to write program? or do it using commandline from shell prompt?
please help me , how i can make it.
Thanks

liquorvicar

I was assuming you're using PHP as this is a PHP forum but it sounds like not. What you want to achieve can be achieved directly in MySQL although this might not be the best place to ask that. If you'll be needing to do this more often, it might be worth building some sort of admin GUI for yourself. In which case, you either need to learn PHP, or get someone in who knows it already (or use one of the many other scripting languages out there).

rosy

I think i have to write a program using php and Mysql
I have to get the maximum ID of the table and insert a record by incrementing the maximum ID.
I have to do it in a loop.
Before inserting a record each time, i have to check for the maximum ID and then increment it and add a record.
This is what i have to do, i think.
As you mentioned, i need to do more often, so its a good idea to create a program.
I will contact PHP forum regarding this.
Thanks a lot for your time, help and guidence. :-)