Help changing update command to trigger (answered)

Current command: MariaDB [sensor_logs]> UPDATE telemetry_log JOIN id_name_update ON telemetry_log.device_id = id_name_update.device_id SET telemetry_log.device_desc = id_name_update.device_desc WHERE telemetry_log.device_desc IS NULL;(this works)

I would like to create a trigger to do this automatically. I have tried 100's of possible triggers configurations, all of them ending with a error either 1064 sql syntax or 1442 same table. Thank you in advance for any direction you all can give me.

table 1 id_name_update

MariaDB [sensor_logs]> show columns in id_name_update;

+-------------+----------+------+-----+---------+-------+

FieldTypeNullKeyDefaultExtra

+-------------+----------+------+-----+---------+-------+

device_idchar(50)NOPRINULL
device_descchar(50)YESNULL
hum_calint(11)NO0
temp_calint(11)NO0

+-------------+----------+------+-----+---------+-------+

MariaDB [sensor_logs]> select * from id_name_update;

+-----------+------------------+---------+----------+

device_iddevice_deschum_caltemp_cal

+-----------+------------------+---------+----------+

11PaaRoomByFreezer00
13Compressor00
15PaaLab00
16PottingArea00
30BondFront00
60RightFreezerAir00
62LeftFreezerAir00
71PaaNewRoom00
72BondBack00
73PrimerBooth00

+-----------+------------------+---------+----------+

Table 2 MariaDB [sensor_logs]> show columns in telemetry_log;

+-----------------+----------+------+-----+---------+----------------+

FieldTypeNullKeyDefaultExtra

+-----------------+----------+------+-----+---------+----------------+

rec_idint(11)NOPRINULLauto_increment
device_idchar(50)YESNULL
typeint(11)YESNULL
valuechar(50)YESNULL
datedatetimeYESMULNULL
unit_of_measurechar(1)YESNULL
device_descchar(50)YESNULL

+-----------------+----------+------+-----+---------+----------------+

MariaDB [sensor_logs]> SELECT * FROM telemetry_log ORDER BY date DESC Limit 10;

+--------+-----------+------+-------+---------------------+-----------------+------------------+

rec_iddevice_idtypevaluedateunit_of_measuredevice_desc

+--------+-----------+------+-------+---------------------+-----------------+------------------+

3854872370.22020-07-01 10:00:11FNULL
3854772552.772020-07-01 10:00:11%NULL
3854672552.772020-07-01 09:59:45%BondBack
385451363.002020-07-01 09:58:52VCompressor
3854413388.362020-07-01 09:58:52FCompressor
3854311371.022020-07-01 09:57:41FPaaRoomByFreezer
3854211558.632020-07-01 09:57:41%PaaRoomByFreezer
3854130370.542020-07-01 09:57:41FBondFront
3854030552.502020-07-01 09:57:41%BondFront
3853971372.52020-07-01 09:57:41FPaaNewRoom

+--------+-----------+------+-------+---------------------+-----------------+------------------+

It may have been as simple as not using phpmyadmin... when i entered the command at the command line : MariaDB [sensor_logs]> CREATE TRIGGER add_desc BEFORE insert on telemetry_log FOR EACH ROW set new.device_desc = ( IT WORKED perfectly.. Thank you for all the help.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.