MySQL: insert new column into a table with default value from other table -

i want add new column 1 table default value other table's column.

1st table 'sensors' ------------------------------------------------------------- id    sensorid      location    city    country     userid      -------------------------------------------------------------  2nd table sensordata ------------------------------- id    sensorid     datavalues   ------------------------------- 

i want add column in sensordata table default value of location sensorid same.

how can that?

something this

alter table  `sensordata` s add  `location` varchar( 200 ) not null default      (select location sensors s s.sensorid = d.sensorid) after  `datavalues` 

please don't tell me why need have duplicate data, there reason :)

you need in 2 steps

step1

alter table  `sensordata`  add  `location` varchar( 200 )  ; 

step2

update sensordata sd join sensors s on s.sensorid = sd.sensorid set sd.location = s.location 

updated: have done , works fine

alter table `sensordata` add `location` varchar(200) null default null after `datavalues`;  update sensordata d set  d.location = (select s.location sensors s s.sensorid = d.sensorid);