PHPMYadmin table Migraton

Question

I need some help with a migration in phpmyadmin

I have 2 Tables with different stucture, I want that it automatically copy the NAME ADRESS LONGITUDE AND LATIDUDE from the [ store ] database to the [ tbl_storefinder_stores ] in the right row, but don't know how to do this.

enter image description here

like a field in the table 'tbl_storefinder_stores' that has the same value as a field in the table 'stores' or do you just want to create new records in stores for every record in tbl_storefinder_stores — Jeremy C., Jun 09 '15 at 08:51
I want update migration only the 4 Fields maybe the ID field too — M6Gpower, Jun 09 '15 at 08:51
Only take the data from the table [ stores ] put it in a new data in the table tbl_sotrefinder_stores but in the right row , like adress to adress and longitude to longitude — M6Gpower, Jun 09 '15 at 08:54
a row is a record, you mean right column, look at my answer at the first query, it is turned around but I will fix that now — Jeremy C., Jun 09 '15 at 08:56

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

Maybe u can try this:

UPDATE table SET columnB = columnA

U can check the answer at here

Copy data into another table

edited May 23 '17 at 12:14

Community

1
1

answered Jun 09 '15 at 08:50

Savage L

51
10

score 1 · Answer 2 · answered Jun 09 '15 at 08:51

1

    UPDATE tbl_storefinder_stores
SET lat = (
    SELECT latitude
    FROM store
    WHERE <condition here> 
    LIMIT 1
)

Condition must reference one of tbl_storefinder_stores field

answered Jun 09 '15 at 08:51

Vhortex

381
1
7

Jeremy C. · Accepted Answer · 2015-06-09T09:14:10.680

1

EDIT: the query you want to be running seems to be get all records from the 'store' table and insert them into tbl_storefinder_stores with the same values like this:

INSERT INTO tbl_storefinder_stores(store_name, store_address, lat, long)
            SELECT name, address, latitude, longitude
    from stores;

fiddle

~~If you want to create new records in the stores table for all of the records in the other table you can just do:~~

INSERT INTO stores(name, address, latitude, longtitude)
VALUES(
SELECT store_name, store_address, lat, long
from tbl_storefinder_stores
);

Or if you want to update the records in stores:

UPDATE stores
SET (name, address, latitude, longtitude)
= (select store_name, store_address, lat, long
   from tbl_storefinder_stores
   where tbl_storefinder_stores.field = stores.field) //field that is the same in both tables and won't change

edited Jun 09 '15 at 09:14

answered Jun 09 '15 at 08:55

Jeremy C.

2,405
1
12
28

thank you for your help but i get this error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT name, address, latitude, longtitude from stores )' at line 3 – M6Gpower Jun 09 '15 at 09:04
i take it your tables are not in the same database? I also just noticed the column for long is called longitude instead of longtitude (sorry i'm not native english speaker and made mistake) – Jeremy C. Jun 09 '15 at 09:06
they are in the same database but longitude and latitude from stores table have type :float and the tbl_storefinder_stores is varchar is that a problem? – M6Gpower Jun 09 '15 at 09:09
and i get the same error again :: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT name, address, latitude, longitude from stores )' at line 3 – M6Gpower Jun 09 '15 at 09:11
@M6Gpower I made a mistake, phpmyadmin works with mysql so the insert statement is slightly different, I will edit it now, should work now – Jeremy C. Jun 09 '15 at 09:13
Thank you , ITS WORKS very well but i have a another question , is there a way that it update the tbl_store automaticly if in the [Stores] table comes a new data ? or i have do this everytime manuel – M6Gpower Jun 09 '15 at 09:25
You think there is a way? :) – M6Gpower Jun 09 '15 at 09:29
you could write a trigger on your stores table, take a look at: https://dev.mysql.com/doc/refman/5.5/en/create-trigger.html – Jeremy C. Jun 09 '15 at 09:33
is this right ? CREATE TRIGGER updater BEFORE UPDATE tbl_storefinder_stores SET (store_name, store_address, lat, lon) SELECT name, address, latitude, longitude from stores; – M6Gpower Jun 09 '15 at 09:42
to be completely honest I haven't written a trigger for a couple of years so I am not completely sure, what exactly are you trying to do? are you trying to edit the row in the new table when the row in the old table changes? or are you trying to add a new row in the new table when someone inserts a new row in the new table – Jeremy C. Jun 09 '15 at 09:47
I am trying to do both , If in table [stores] anything change that it change the [tbl_storefinder_stores] table too and if in the table [stores] i add a new row that [tbl_storefinder_stores] add it too , just like an updater it update the 4(name/adress/latitude/longitude) things always from the [store] table , – M6Gpower Jun 09 '15 at 09:54
for the update you need a field between your record in stores and in storefinder_stores that is the same for both records (like an id or something), maybe you should post a new question though, I'm not really 100% sure I can help you with this – Jeremy C. Jun 09 '15 at 09:58

PHPMYadmin table Migraton

3 Answers3