mysql - How to copy fields from one table to another -

mysql - How to copy fields from one table to another -

i have searched , tried have failed , wondering if help unusual twist copying info 1 table another.

i collecting info own weather station piece of software written else in c. have had @ open source code hard read uses lot of single character variables etc. writes info mysql database table in sequential format this:-

create table readings ( `date` timestamp not null default current_timestamp on update current_timestamp, `name` text not null, `value` double not null, `id` int(11) not null auto_increment )

where each sensor reading stored on seperate row. name denotes sensor name such temperature sensor or raingauge, windspeed sensor etc, etc. sensors updated every min on minute. problem finding way of storing info makes extremely hard perform queries on have include lot of conditionals in query, such checking 'name' field "rgc1" raingauge counter etc. complicates things raingauge sensor counter , not instant rainfall rate. have perform query on database , subtract previous raingauge counter value current 1 number of counts between 2 occasions these read. each count 0.01 inches of rainfall, multiplying count difference 0.01 gives rainfall rate inches/interval. temperature sensors little easier instant temperature values still complicated making comparances between different occasions.

i either need way of calculating info leaving database construction (which have failed dismally) or re-create new info every min comes in database sensor readings stored on same row, this:-

create table `readings2` ( `id` bigint(20) not null auto_increment, `readdate` datetime null default null, `ms-th-temp` float null default null, `ms-th-hum` float unsigned null default null, `utemp` float null default null, `uv` float unsigned null default null, `wspd` smallint(5) unsigned null default '0', `wdir` tinyint(3) unsigned null default '0', `ws-temp` float null default '0', `rgc0` bigint(20) unsigned null default '0', `rgc1` bigint(20) unsigned null default '0', `lc0` bigint(20) unsigned null default '0', `lc1` bigint(20) unsigned null default '0', `prs0` float unsigned null default '0', `tai8570-temp` float unsigned null default '0', primary key (`id`) )

i running of on ubuntu server 11.04 , copying info done on cron job every min ruby script or similar. can help? have searched , tried lot of suggestions site have failed in efforts come solution.

further original post above, have been doing lot of messing around getting persistent syntax errors next code damned if can see wrong. if comment out insert statement, syntax error goes away. can't see wrong syntax in insert, though:-

delimiter $$ drop trigger /*!50033 if exists */ `copyfields`$$ create trigger `copyfields` after update on `readings` each row begin declare mychanged int default 0; declare newdate datetime default null; declare newmsthtemp float default null; declare newmsthhum float default null; declare newutemp float default null; declare newuv float default null; declare newwspd smallint default null; declare newwdir tinyint default null; declare newwstemp float default null; declare newrgc0 bigint default null; declare newrgc1 bigint default null; declare newlc0 bigint default null; declare newlc1 bigint default null; declare newprs0 float default null; declare newtai8570temp float default null; if new.name = "utemp" set mychanged = 1; end if; if mychanged = 1 set newdate = new.date; if new.name = 'ms-th-temp' set newmsthtemp = new.value; end if; if new.name = 'ms-th-hum' set newmsthhum = new.value; end if; if new.name = 'wspd' set newwspd = new.value; end if; if new.name = 'wdir' set newwdir = new.value; end if; if new.name = 'ws-temp' set newwstemp = new.value; end if; if new.name = 'rgc0' set newrgc0 = new.value; end if; if new.name = 'rgc1' set newrgc1 = new.value; end if; if new.name = 'utemp' set newutemp = new.value; end if; if new.name = 'uv' set newuv = new.value; end if; insert readings2 (readdate, ms-th-temp, ms-th-hum, utemp, uv, wspd, wdir, ws-temp, rgc0, rgc1, lc0, lc1, prs0, tai8570-temp) values (newdate, newmsthtemp, newmsthhum, newutemp, newuv, newwspd, newwdir, newwstemp, newrgc0, newrgc1, newlc0, newlc1, newprs0, newtai8570temp); end if;

end$$

delimiter ;

the error getting above is:-

sql error (1064): have error in sql syntax; check manual corresponds mysql server version right syntax utilize near '-th-temp, ms-th-hum, utemp, uv, wspd, wdir, ws-temp, ' @ line 53

it looks more complicated if help point me in right direction this, grateful.

thanks in advance.

i'd alter table definition tiny bit, fields null (as in unknown) until they're explicitly filled in. other that, utilize insert trigger , update trigger, e.g.:

create trigger readings_insert after insert on readings each row begin -- upsert readings2 end;

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

mysql ruby