Page 1 of 1

transfer data

Posted: Thu Apr 04, 2013 7:34 am
by wckf
hi guys,
how can i transfer data from one table to another using phpmyadmin sql query ,
both have the same primary key

Re: transfer data

Posted: Thu Apr 04, 2013 8:07 am
by seandisanti
INSERT INTO table2(field1,field2,field3) SELECT field1,field2,field3 from table1 where condition = true

Re: transfer data

Posted: Thu Apr 04, 2013 8:14 am
by wckf
this is what happened when i entered your code
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition = true' at line 5


otherwise when i do it without the WHERE condition = true
it gives me this :
#1452 - Cannot add or update a child row: a foreign key constraint fails

so my main problem is this :

#1452 - Cannot add or update a child row: a foreign key constraint fails

Re: transfer data

Posted: Thu Apr 04, 2013 8:15 am
by seandisanti
I'm sorry, what i posted was a template, not a solution. you have to plug in the field names you want to insert and select, the table names to move data from and to, and boolean condition to evaluate the rows upon to select which records get migrated.

Re: transfer data

Posted: Thu Apr 04, 2013 9:02 am
by wckf
already did that maybe it is because both of them have to same primary key ?

Re: transfer data

Posted: Thu Apr 04, 2013 9:21 am
by seandisanti
You can't have duplicate values in the primary key field. it would probably be easiest for everyone to understand if you post the fields etc associated with each table.

Re: transfer data

Posted: Thu Apr 04, 2013 9:46 am
by wckf
.

Re: transfer data

Posted: Thu Apr 04, 2013 9:58 am
by wckf
guys can i put a foreign key auto increment ?
with letters like :
lea1
lea2
lea3
.
.
.
?

Re: transfer data

Posted: Thu Apr 04, 2013 10:33 am
by seandisanti
a foreign key is a primary key in another table, so it should already be autoincremented in the table where it is the primary.

your other question about having assigned id's reuse deleted id's actually requires a little more work. once an id has been used, it is removed from the range of id's to be auto assigned. so you can either manually assign the id at the time of insert by using a subquery to add one to the max() value in the field in the table.

Alternatively...
THIS WILL DELETE ALL DATA IN THE TABLE DO NOT DO IT UNLESS THAT IS WHAT YOU WANT

If you're starting from scratch and scrapping all of the data in the table, and just want to begin with one instead of whatever the next number was; you can truncate the table (either using truncate table command, or select the table in phpmyadmin and go to operations, and select truncate).

Re: transfer data

Posted: Thu Apr 04, 2013 10:56 am
by wckf
what i meant is i have a field in a table where i want it to auto increment with letters nside
e.g :
lea1
lea2
lea3
.
.
.
forget about the foreign key thing :)

Re: transfer data

Posted: Thu Apr 04, 2013 11:11 am
by seandisanti
Probably the easiest way to achieve that would be with an insert trigger. you could have it populate a field with the contents of a string field and an auto increment field as the row is inserted. search for mysql insert trigger to get a good example

Re: transfer data

Posted: Thu Apr 04, 2013 11:17 am
by wckf
UPDATE leads SET lead_no='LEA'+cast(leadid as char)

this took the number from the id field and puted it nto the lead_no field but without the LEA

Re: transfer data

Posted: Thu Apr 04, 2013 8:26 pm
by johnj
It is better to increment the number in the code and then update it using the sql. e.g. if the current value was lea001, then increment 001 to 002, concatenate and create a new string, and use that for the update so that the new value will be lea002.